Skip site navigation (1) Skip section navigation (2)

Re: Wrong plan sequential scan instead of an index one

From: Matteo Beccati <php(at)beccati(dot)com>
To: Gaetano Mendola <mendola(at)bigfoot(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, Michael Fuhr <mike(at)fuhr(dot)org>
Subject: Re: Wrong plan sequential scan instead of an index one
Date: 2007-03-30 13:36:26
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Erik JonesDate: 2007-03-30 14:14:11
Subject: Re: Shared buffers, db transactions commited, and write IO on Solaris
Previous:From: Sorin N. CiolofanDate: 2007-03-30 13:20:09
Subject: Re: ERROR: out of shared memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group