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
