[GENERAL] Possible dump/restore bug
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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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: William Yu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible dump/restore bug
William Yu [EMAIL PROTECTED] 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
Re: [GENERAL] Possible dump/restore bug
William Yu [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
