On 06/04/11 11:40, Mark Kirkwood wrote:
On 06/04/11 11:31, Scott Marlowe wrote:
On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown<thom@xxxxxxxxx> wrote:
I'm using 9.1dev.
SNIP
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.
Works fine for me:
explain analyze select * from indextest where stuff = 'bark';
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indextest_stuff on indextest (cost=0.00..837.01
rows=13347 width=9) (actual time=0.226..6.073 rows=8000 loops=1)
Index Cond: (stuff = 'bark'::text)
Total runtime: 7.527 ms
Even with a random_page_cost = 4 it works. Running 8.3.13 btw.
I reproduce what Thom sees - using 9.1dev with default config
settings. Even cranking up effective_cache_size does not encourage the
partial index to be used.
However trying with 9.0 gives me the (expected) same 8.3 behaviour:
test=# CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff =
'bark';
CREATE INDEX
test=# explain analyze select * from indextest where stuff = 'bark';
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using indextest_stuff on indextest (cost=0.00..284.20
rows=5873 width=9)
(actual
time=0.276..9.621 rows=8000 loops=1)
Index Cond: (stuff = 'bark'::text)
Total runtime: 16.621 ms
(3 rows)
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
