Re: Partial index slower than regular index

On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> I'm using 9.1dev.
SNIP

> DROP INDEX indextest_stuff;
>
> CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff = 'bark';
>
> postgres=# explain analyze select * from indextest where stuff = 'bark';
>                                                    QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------
>  Seq Scan on indextest  (cost=0.00..143386.48 rows=5606 width=9)
> (actual time=164.321..1299.794 rows=8000 loops=1)
>   Filter: (stuff = 'bark'::text)
>  Total runtime: 1300.267 ms
> (3 rows)
>
> The index doesn't get used.  There's probably a logical explanation,
> which is what I'm curious about.

Works fine for me:

explain analyze select * from indextest where stuff = 'bark';
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indextest_stuff on indextest (cost=0.00..837.01
rows=13347 width=9) (actual time=0.226..6.073 rows=8000 loops=1)
Index Cond: (stuff = 'bark'::text)
Total runtime: 7.527 ms

Even with a random_page_cost = 4 it works. Running 8.3.13 btw.