Обсуждение: 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.

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

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
>

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