Náhrada zbytnělých (bloated) indexů

• klonování indexů:

psql -At -c "select (select replace(replace(replace(indexdef,'INDEX','INDEX CONCURRENTLY'),'_pkey','_pk2'),'_idx','_ix2') from pg_indexes 
where indexname=c.relname) from pg_class c where relkind = 'i' and pg_table_size(c.oid) > 1024*1024*1024*4.0;" mydb | psql -S mydb

• promazání neklíčových indexů:

psql -At -c "select 'drop index ' || relname from pg_class c where relkind = 'i' and pg_table_size(c.oid) > 1024*1024*1024*4.0 and relname not like '%_pkey'" mydb | psql -S mydb

• přejmenování neklíčových indexů na původní název:

psql -At  -c "select 'alter index ' || indexname || ' rename to ' || replace(indexname, '_ix2','_idx') from pg_indexes where indexname like '%_ix2'" mydb | psql -S mydb

• náhrada klíčových indexů v omezení:

alter table f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhvas4ph 
  drop constraint f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhv_pkey,
  add constraint f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhv_pkey PRIMARY KEY USING INDEX f_campaign_aaabx07i2nayhun_aaabyxpbdtayldf_aaabz_aaab10pbhv_pk2;

• reindexace menších indexů:

psql -At -c "select 'REINDEX INDEX' || c.relname from pg_class c where relkind = 'i' and pg_table_size(c.oid) < 1024*1024*1024*2.0;" mydb | psql -S mydb

Skript pro vygenerování příkazů pro vybranou tabulku:

do $$
declare 
  r record;
  newname text;
begin
  for r in 
    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),
           c.oid::regclass as tablename
     FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
          LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
     WHERE c.oid = 'f_clienthistory_abbbvouh0farz2u_aamllp9oe8abeeo__abd29wbjguact8w'::regclass AND c.oid = i.indrelid AND i.indexrelid = c2.oid
  loop
    newname = 'fresh_' || md5(r.indexname || r.tablename);
    insert into commands values(replace(replace(r.indexdef, 'INDEX', 'INDEX CONCURRENTLY'), r.indexname, newname) || ';');
    if r.indisprimary then
      insert into commands values(format('ALTER TABLE %I DROP CONSTRAINT %I, ADD CONSTRAINT %I PRIMARY KEY USING INDEX %I;',
                r.tablename, r.indexname, r.indexname, newname));
    else
      insert into commands values(format('BEGIN;DROP INDEX %I;', r.indexname) || format('ALTER INDEX %I RENAME TO %I; COMMIT;', newname, r.indexname));
    end if;
    raise notice 'refreshed index: %', r.indexname;
  end loop;
end;
$$ language plpgsql;