Re: Partial index slower than regular index - Mailing list pgsql-performance

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