The following bug has been logged online:
Bug reference: 1286
Logged by: Federico Di Gregorio
Email address: fog@initd.org
PostgreSQL version: 7.4.5
Operating system: Debian GNU/Linux sarge
Description: indices not used after a pg_restore
Details:
We have a (big) database with a lot of functional indices (the indices are
quite strange but should replicate an old ISAM sorting procedure).
After a pg_dump/pg_restore (using the tar format) queries that were using
the indices don't use them anymore until the indices are dropped and
recreated. After that the indices are used the correct way.
Note that after the pg_restore we also tried a complete
VACUUM/ANALYZE/REINDEX but the situation does not change. The indices are
not used until dropped and recreated.
Please, if you discuss this on the bugs mailing list keep me in cc:.
Example of one of the indices:
CREATE INDEX "MOVIMENTII5" ON movimenti USING btree
(upper(((to_char("TYPE_REF", 'S0000000000'::text) || to_char("IDREF",
'S0000000000'::text)) || to_char("IDMOVIMENT", 'S0000000000'::text))));