Date: Tue, 5 Apr 2011 18:02:51 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Thom Brown <thom@linux.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partial index slower than regular index
Message-ID: <20110405230251.GD26726@aart.is.rice.edu>
References: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)

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