From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Thom Brown <thom@linux.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partial index slower than regular index
Date: Wed, 06 Apr 2011 14:03:53 +1200
Message-ID: <4D9BCA09.5020307@catalyst.net.nz> (raw)
In-Reply-To: <4D9BA87B.4090500@catalyst.net.nz>
References: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
<BANLkTi=izsG5SVwMW_28Dg2-0M__kSTg=A@mail.gmail.com>
<4D9BA87B.4090500@catalyst.net.nz>
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@linux.com> 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
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: mark.kirkwood@catalyst.net.nz, scott.marlowe@gmail.com, thom@linux.com
Subject: Re: Partial index slower than regular index
In-Reply-To: <4D9BCA09.5020307@catalyst.net.nz>
* 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