Re: BUG #1286: indices not used after a pg_restore

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))));

"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> 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.

I do not believe that you remembered to ANALYZE after restore.

regards, tom lane

On Thu, 2004-10-14 at 06:54 -0400, Tom Lane wrote:
> "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> > 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.
>
> I do not believe that you remembered to ANALYZE after restore.

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.

--
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

Federico Di Gregorio <fog(at)initd(dot)org> writes:
> On Thu, 2004-10-14 at 06:54 -0400, Tom Lane wrote:
>> I do not believe that you remembered to ANALYZE after restore.

> 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.

I'd like to see it, please.

regards, tom lane

Federico Di Gregorio <fog(at)initd(dot)org> writes:
> ok. attached to this mail is a dump in tar format. this is the EXPLAIN
> ANALYZE of a query *before* the dump:

> EXPLAIN ANALYZE SELECT * FROM BOL_USC
> WHERE UPPER(RPAD("RAGIONE",80)||TO_CHAR("IDBOL_USC",'S0000000000')) >=
> ' +0000000000'
> ORDER BY UPPER(RPAD("RAGIONE",80)||TO_CHAR("IDBOL_USC",'S0000000000')) ASC
> LIMIT 2;

> Limit (cost=0.00..5.99 rows=2 width=1279) (actual time=154.868..170.753 rows=2 loops=1)
> -> Index Scan using "BOL_USCI3" on bol_usc (cost=0.00..20539.92 rows=6859 width=1279) (actual time=154.859..170.734 rows=2 loops=1)
> Index Cond: (upper((rpad(("RAGIONE")::text, 80, ' '::text) || to_char("IDBOL_USC", 'S0000000000'::text))) >= ' +0000000000'::text)
> Total runtime: 171.106 ms

> [ but after dump and restore this turns into a sequential scan ]

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