Re: Possible dump/restore bug - Mailing list pgsql-general

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