Re: Possible dump/restore bug

William Yu <wyu(at)talisys(dot)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