MIME-Version: 1.0
From: Thom Brown <thom@linux.com>
Date: Tue, 5 Apr 2011 23:35:29 +0100
Message-ID: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
Subject: Partial index slower than regular index
To: pgsql-performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1

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.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company1