Message-ID: <460D125A.4030101@beccati.com>
Date: Fri, 30 Mar 2007 15:36:26 +0200
From: Matteo Beccati <php@beccati.com>
User-Agent: Mozilla Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Gaetano Mendola <mendola@bigfoot.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	Michael Fuhr <mike@fuhr.org>
Subject: Re: Wrong plan sequential scan instead of an index one
References: <460CD941.6080303@bigfoot.com>
	<b41c75520703300251oe916648r75c16c1bf70cd10@mail.gmail.com>
	<460CE19A.3060909@bigfoot.com>
	<20070330104611.GA1540@winnie.fuhr.org>
	<20070330105534.GA1737@winnie.fuhr.org>
	<460CF5E4.3090005@bigfoot.com>
In-Reply-To: <460CF5E4.3090005@bigfoot.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Gaetano Mendola wrote:
> Michael Fuhr wrote:
>> On Fri, Mar 30, 2007 at 04:46:11AM -0600, Michael Fuhr wrote:
>>> Have you tried increasing the statistics target on l_pvcp.value?
>>> I ran your queries against canned data in 8.2.3 and better statistics
>>> resulted in more accurate row count estimates for this and other
>>> parts of the plan.  I don't recall if estimates for non-leading-character
>>> matches in earlier versions can benefit from better statistics.
>> This might work only in 8.2.  I see the following in the Release Notes:
>>
>> * Improve the optimizer's selectivity estimates for LIKE, ILIKE,
>>   and regular expression operations (Tom)
> 
> 
> I will try same select on a 8.2 ( that one was a 8.1 ) and I'll let you
> know.

You will also need to set statistics for the column to at least 100 to 
trigger the improved selectivity estimate if memory serves.

Not enough time to check the code, but Tom could better advise.


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com