Re: Best practice when reindexing in production - Mailing list pgsql-performance

From: Matheus de Oliveira [mailto:matioli.matheus@gmail.com]
Sent: Wednesday, May 29, 2013 10:19 AM
To: Igor Neyman
Cc: Niels Kristian Schjødt; Magnus Hagander; pgsql-performance@postgresql.org list
Subject: Re: [PERFORM] Best practice when reindexing in production



On Wed, May 29, 2013 at 10:55 AM, Igor Neyman <ineyman@perceptron.com> wrote:


You could do something like this (which considers you use simple names for your indexes, where simple
~ [a-z_][a-z0-9_]*):

SELECT 
regexp_replace(i.indexdef, '^CREATE( UNIQUE)? INDEX (.*) ON (.*)', 'CREATE\1 INDEX CONCURRENTLY tmp_\2 ON \3;') ||
E'\n'
|| E'BEGIN;\n'
|| 'DROP INDEX ' || i.indexname || E';\n'
|| 'ALTER INDEX tmp_' || i.indexname || ' RENAME TO ' || i.indexname || E';\n'
|| E'COMMIT;\n'
FROM pg_indexes i
WHERE schemaname !~ '^(pg_|information_schema$)';

Although this one is *really simple* and *error phrone*, because it does not consider at least two things: index that
areconstraints and index that has FK depending on it. For the first case, you only need to change the constraint to use
theindex and the DROP command. As for the second case, you would need to remove the FKs, drop the old one and recreate
theFK (inside a transaction, of course), but this could be really slow, a reindex for this case would be simpler and
perhapsfaster. 

=================
I must be missing something here.
But, how is that FK depends on the index?
I understand FK lookup works much faster with the index supporting FK than without it, but you could have FK without
index(on the "child" table). 
So, what gives?

AFAIK, when you create a FK, PostgreSQL associate it with an UNIQUE INDEX on the target table. It creates an entry on
pg_depends(I don't know if somewhere else), and when you try to drop the index, even if there is an identical one that
PGscould use, it will throw an error. 

You can easily check this:

postgres=# CREATE TABLE parent(id int);
CREATE TABLE
postgres=# CREATE UNIQUE INDEX parent_idx1 ON parent (id);
CREATE INDEX
postgres=# CREATE TABLE child(idparent int REFERENCES parent (id));
CREATE TABLE
postgres=# CREATE UNIQUE INDEX parent_idx2 ON parent (id);
CREATE INDEX
postgres=# DROP INDEX parent_idx1;
ERROR:  cannot drop index parent_idx1 because other objects depend on it
DETAIL:  constraint child_idparent_fkey on table child depends on index parent_idx1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

BTW, I do think PostgreSQL could verify if there is another candidate to this FK. Is it in TODO list? Should it be?

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres


So, it's about index on parent table that's used for unique (or PK) constraint and referenced by FK on child table.
From your previous email I thought that index on child table supporting FK (which is mostly created for performance
purposes)cannot be dropped without disabling FK. My bad. 

Igor Neyman