http://postgres.cz/index.php?action=history&feed=atom&title=N%C3%A1hrada_zbytn%C4%9Bl%C3%BDch_%28bloated%29_index%C5%AF 2026-05-13T01:37:07Z Historie editací této stránky MediaWiki 1.43.3 http://postgres.cz/index.php?title=N%C3%A1hrada_zbytn%C4%9Bl%C3%BDch_(bloated)_index%C5%AF&diff=571&oldid=prev 2016-01-04T19:16:50Z

<p></p> <p><b>Nová stránka</b></p><div>* klonování indexů:<br /> &lt;pre&gt;psql -At -c &quot;select (select replace(replace(replace(indexdef,&#039;INDEX&#039;,&#039;INDEX CONCURRENTLY&#039;),&#039;_pkey&#039;,&#039;_pk2&#039;),&#039;_idx&#039;,&#039;_ix2&#039;) from pg_indexes <br /> where indexname=c.relname) from pg_class c where relkind = &#039;i&#039; and pg_table_size(c.oid) &gt; 1024*1024*1024*4.0;&quot; mydb | psql -S mydb&lt;/pre&gt;<br /> * promazání neklíčových indexů:<br /> &lt;pre&gt;psql -At -c &quot;select &#039;drop index &#039; || relname from pg_class c where relkind = &#039;i&#039; and pg_table_size(c.oid) &gt; 1024*1024*1024*4.0 and relname not like &#039;%_pkey&#039;&quot; mydb | psql -S mydb&lt;/pre&gt;<br /> * přejmenování neklíčových indexů na původní název:<br /> &lt;pre&gt;psql -At -c &quot;select &#039;alter index &#039; || indexname || &#039; rename to &#039; || replace(indexname, &#039;_ix2&#039;,&#039;_idx&#039;) from pg_indexes where indexname like &#039;%_ix2&#039;&quot; mydb | psql -S mydb&lt;/pre&gt;<br /> * náhrada klíčových indexů v omezení:<br /> &lt;pre&gt;alter table f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhvas4ph <br /> drop constraint f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhv_pkey,<br /> add constraint f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhv_pkey PRIMARY KEY USING INDEX f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhv_pk2;&lt;/pre&gt;<br /> * reindexace menších indexů:<br /> &lt;pre&gt;psql -At -c &quot;select &#039;REINDEX INDEX&#039; || c.relname from pg_class c where relkind = &#039;i&#039; and pg_table_size(c.oid) &lt; 1024*1024*1024*2.0;&quot; mydb | psql -S mydb&lt;/pre&gt;<br /> <br /> Skript pro vygenerování příkazů pro vybranou tabulku:<br /> &lt;pre&gt;<br /> do $$<br /> declare <br /> r record;<br /> newname text;<br /> begin<br /> for r in <br /> SELECT c2.relname as indexname, i.indisprimary, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as indexdef, pg_catalog.pg_get_constraintdef(con.oid, true),<br /> c.oid::regclass as tablename<br /> FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i<br /> LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN (&#039;p&#039;,&#039;u&#039;,&#039;x&#039;))<br /> WHERE c.oid = &#039;f_clienthistory_abbbvouh0farz2u_aamllp9oe8abeeo__abd29wbjguact8w&#039;::regclass AND c.oid = i.indrelid AND i.indexrelid = c2.oid<br /> loop<br /> newname = &#039;fresh_&#039; || md5(r.indexname || r.tablename);<br /> insert into commands values(replace(replace(r.indexdef, &#039;INDEX&#039;, &#039;INDEX CONCURRENTLY&#039;), r.indexname, newname) || &#039;;&#039;);<br /> if r.indisprimary then<br /> insert into commands values(format(&#039;ALTER TABLE %I DROP CONSTRAINT %I, ADD CONSTRAINT %I PRIMARY KEY USING INDEX %I;&#039;,<br /> r.tablename, r.indexname, r.indexname, newname));<br /> else<br /> insert into commands values(format(&#039;BEGIN;DROP INDEX %I;&#039;, r.indexname) || format(&#039;ALTER INDEX %I RENAME TO %I; COMMIT;&#039;, newname, r.indexname));<br /> end if;<br /> raise notice &#039;refreshed index: %&#039;, r.indexname;<br /> end loop;<br /> end;<br /> $$ language plpgsql;<br /> <br /> &lt;/pre&gt;</div>

imported>Pavel