From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | William Yu <wyu(at)talisys(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Possible dump/restore bug |
Date: | 2004-12-13 22:59:38 |
Message-ID: | 28997.1102978778@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dann Corbit | 2004-12-13 23:11:11 | Re: Performance differences 7.1 to 7.3 |
Previous Message | Jimmie H. Apsey | 2004-12-13 22:43:29 | Performance differences 7.1 to 7.3 |