From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Partial index slower than regular index |
Date: | 2011-04-05 22:35:29 |
Message-ID: | BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Marshall | 2011-04-05 23:02:51 | Re: Partial index slower than regular index |
Previous Message | Maria L. Wilson | 2011-04-05 19:25:46 | help speeding up a query in postgres 8.4.5 |