Possible dump/restore bug

Started by William Yuover 21 years ago4 messagesgeneral
Jump to latest
#1William Yu
wyu@talisys.com

It seems that upon dump & restore, UPPER indexes either aren't recreated
correctly or not listed somewhere the query analyzer can know it exist.
I've encountered first encountered this problem doing an upgrade to
7.3.7 to 7.4.6. I again encountered this program replicating a server
(same 7.4.6 on both source & dest). Dropping the index and creating it
again seems to fix the issue.

This server isn't slated to go live for another few weeks so I can leave
it in this semi-crippled state for some debugging/testing if needed.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: William Yu (#1)
Re: Possible dump/restore bug

William Yu <wyu@talisys.com> writes:

It seems that upon dump & restore, UPPER indexes either aren't recreated
correctly or not listed somewhere the query analyzer can know it exist.

Seems unlikely. Perhaps you forgot to ANALYZE after reloading?

regards, tom lane

#3William Yu
wyu@talisys.com
In reply to: Tom Lane (#2)
Re: Possible dump/restore bug

Certainly did analyze. Here's the query plans. Note the non-UPPER query
uses an indexscan just fine.

INFO: analyzing "public.fin_vendors"
INFO: "fin_vendors": 4207 pages, 3000 rows sampled, 63063 estimated
total rows
ANALYZE

talisman=# explain analyze select * from fin_vendors where name like
'NBC%'\g
--------------------------------------------------------------------
Index Scan using idx_finvendors_name on fin_vendors (cost=0.00..4.01
rows=1 width=600) (actual time=0.029..0.036 rows=2 loops=1)
Index Cond: ((name >= 'NBC'::bpchar) AND (name < 'NBD'::bpchar))
Filter: (name ~~ 'NBC%'::text)
Total runtime: 0.087 ms
(4 rows)

talisman=# explain analyze select * from fin_vendors where UPPER(name)
like 'NBC%'\g
--------------------------------------------------------------------
Seq Scan on fin_vendors (cost=0.00..5310.60 rows=316 width=600)
(actual time=18.080..104.956 rows=2 loops=1)
Filter: (upper((name)::text) ~~ 'NBC%'::text)
Total runtime: 105.061 ms
(3 rows)

I can confirm Postgres thinks there's an index somewhere in the system
already. Note that none of these indexes were created by hand so it is
not a fat-finger error.

talisman=# create index idx_finvendors_upper_name on fin_vendors
(upper(name))\g
ERROR: relation "idx_finvendors_upper_name" already exists

Since I don't want to drop these seemingly broken indexes just yet, I'll
"recreate" the index by using a new name:

talisman=# create index test_upper on fin_vendors (upper(name))\g
CREATE INDEX
talisman=# analyze fin_vendors\g
ANALYZE
talisman=# explain analyze select * from fin_vendors where upper(name)
like 'NBC%'\g
--------------------------------------------------------------------
Index Scan using test_upper on fin_vendors (cost=0.00..616.68
rows=316 width=604) (actual time=0.032..0.039 rows=2 loops=1)
Index Cond: ((upper((name)::text) >= 'NBC'::text) AND
(upper((name)::text) < 'NBD'::text))
Filter: (upper((name)::text) ~~ 'NBC%'::text)
Total runtime: 0.096 ms
(4 rows)

Tom Lane wrote:

Show quoted text

William Yu <wyu@talisys.com> writes:

It seems that upon dump & restore, UPPER indexes either aren't recreated
correctly or not listed somewhere the query analyzer can know it exist.

Seems unlikely. Perhaps you forgot to ANALYZE after reloading?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: William Yu (#3)
Re: Possible dump/restore bug

William Yu <wyu@talisys.com> writes:

Index Scan using idx_finvendors_name on fin_vendors (cost=0.00..4.01
rows=1 width=600) (actual time=0.029..0.036 rows=2 loops=1)
Index Cond: ((name >= 'NBC'::bpchar) AND (name < 'NBD'::bpchar))
Filter: (name ~~ 'NBC%'::text)

Hmm. Apparently column "name" is of type char(N) rather than text?

talisman=# create index test_upper on fin_vendors (upper(name))\g
CREATE INDEX
talisman=# explain analyze select * from fin_vendors where upper(name)
like 'NBC%'\g
--------------------------------------------------------------------
Index Scan using test_upper on fin_vendors (cost=0.00..616.68
rows=316 width=604) (actual time=0.032..0.039 rows=2 loops=1)
Index Cond: ((upper((name)::text) >= 'NBC'::text) AND
(upper((name)::text) < 'NBD'::text))
Filter: (upper((name)::text) ~~ 'NBC%'::text)
Total runtime: 0.096 ms

Note the inserted casts: the index is really on UPPER(name::text).
It was probably shown that way in your dump file.

I believe what is happening here is that pre-8.0 PG versions fail to
recognize that implicit and explicit casting to text are equivalent
operations, and so an index declared as "create index foo_upper on
foo (upper(name::text))" isn't going to match a query that mentions
"upper(name)" with no cast. This is a slightly tricky issue because
there are in fact cases where implicit and explicit casts have different
semantics :-(. I think we've got it worked out properly in 8.0 though.

regards, tom lane