Message-ID: <4D9BA87B.4090500@catalyst.net.nz>
Date: Wed, 06 Apr 2011 11:40:43 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110223 Lightning/1.0b2 Thunderbird/3.1.8
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: Thom Brown <thom@linux.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partial index slower than regular index
References: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
	<BANLkTi=izsG5SVwMW_28Dg2-0M__kSTg=A@mail.gmail.com>
In-Reply-To: <BANLkTi=izsG5SVwMW_28Dg2-0M__kSTg=A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

Mark