Re: Partial index slower than regular index

On Tue, Apr 05, 2011 at 11:35:29PM +0100, Thom Brown wrote:
> I'm using 9.1dev.
>
> Could someone explain the following behaviour?
>
> -- create a test table
> CREATE TABLE indextest (id serial, stuff text);
>
> -- insert loads of values with intermittent sets of less common values
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
> INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000);
> INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000);
>
> -- create regular index
> CREATE INDEX indextest_stuff ON indextest(stuff);
>
> -- update table stats
> ANALYZE indextest;
>
> postgres=# explain analyze select * from indextest where stuff = 'bark';
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------
> Index Scan using indextest_stuff on indextest (cost=0.00..485.09
> rows=9076 width=9) (actual time=0.142..3.533 rows=8000 loops=1)
> Index Cond: (stuff = 'bark'::text)
> Total runtime: 4.248 ms
> (3 rows)
>
> This is very fast. Now if I drop the index and add a partial index
> with the conditions being tested.
>
> 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.
>

The stats seem off. Are you certain that an analyze has run?

Cheers,
Ken