On 6 April 2011 05:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> The index doesn't get used. There's probably a logical explanation,
>> which is what I'm curious about.
>
> Er ... it's broken?
>
> It looks like the index predicate expression isn't getting the right
> collation assigned, so predtest.c decides the query doesn't imply the
> index's predicate. Too tired to look into exactly why right now, but
> it's clearly bound up in all the recent collation changes.
Testing it again with very explicit collations, it still has issues:
CREATE INDEX indextest_stuff ON indextest(stuff COLLATE "en_GB.UTF-8")
WHERE stuff COLLATE "en_GB.UTF-8" = 'bark' COLLATE "en_GB.UTF-8";
postgres=# explain analyze select * from indextest where stuff collate
"en_GB.UTF-8" = 'bark' collate "en_GB.UTF-8";
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on indextest (cost=0.00..143387.00 rows=8312 width=9)
(actual time=163.759..1308.316 rows=8000 loops=1)
Filter: ((stuff)::text = 'bark'::text COLLATE "en_GB.UTF-8")
Total runtime: 1308.821 ms
(3 rows)
But I'm possibly missing the point here.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
