The following bug has been logged online:

Bug reference: 1286
Logged by: Federico Di Gregorio

Email address: fog (AT) initd (DOT) 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))));



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
I do not believe that you remembered to ANALYZE after restore.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Federico Di Gregorio <fog (AT) initd (DOT) org> writes:
I'd like to see it, please.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--=-1gtvU7xiWaZmkglx0+2Z
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2004-10-14 at 06:54 -0400, Tom Lane wrote:
unfortunately for your belief, i remembered.

also, this problem can be replicated at will. i can send a dump that
exposes the problem if necessary but i'll need some time to purge
customers data and create a dump a can freely send.

--=20
Federico Di Gregorio http://people.initd.org/fog
Debian GNU/Linux Developer fog (AT) debian (DOT) org
INIT.D Developer fog (AT) initd (DOT) org
Debian. The best software from the best people [see above]
-- brought to you by One Line Spam

--=-1gtvU7xiWaZmkglx0+2Z
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQBBbl2/vcCgrgZGjesRAinbAJ9JPhhNMnqfPcoKVCJuT48Kj9mspwCeLH Kc
CA6RAuTAjPyWNhIr3snTj3o=
=BxXf
-----END PGP SIGNATURE-----

--=-1gtvU7xiWaZmkglx0+2Z--

Federico Di Gregorio <fog (AT) initd (DOT) org> writes:
OK, I see the problem. The dump script dumps the index definition as

CREATE INDEX "BOL_USCI3" ON bol_usc USING btree (upper((rpad(("RAGIONE")::text, 80) || to_char("IDBOL_USC", 'S0000000000'::text))));

Note that the argument of rpad() is explicitly coerced to text in the
dump, whereas it is not in your query. If you create the index without
writing that coercion, or if you write ::text in the query, then the
index is successfully matched to the query.

The explicit coercion is not supposed to matter, and indeed it does not
just next door in the to_char() call. I think that the problem may be
related to the fact that 2-parameter rpad() is a SQL function that gets
replaced inline with a call to 3-parameter rpad(). Somehow that's
messing up the recognition that implicit vs. explicit coercion does not
matter.

The problem seems already fixed in CVS tip (8.0 beta) and looking at the
change history I note that 8.0 uses a much cleaner mechanism for
ensuring that this works properly. It's probably not very practical to
backpatch a fix however.

For the moment, your workaround is just to drop and recreate the
BOL_USCI3 index without the explicit coercion.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster