From: Thom Brown <thom@linux.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Partial index slower than regular index
Date: Tue, 5 Apr 2011 23:35:29 +0100
Message-ID: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: thom@linux.com
Subject: Re: Partial index slower than regular index
In-Reply-To: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox