Re: Partial index slower than regular index

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