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

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