Re: Query plan choice issue

On 14/09/2010, at 10:37 AM, Yaroslav Tykhiy wrote:
>
> On 14/09/2010, at 12:41 AM, Tom Lane wrote:
>
>> Yaroslav Tykhiy <yar@barnet.com.au> writes:
[...]
>>
>> I think the major problem you're having is that the planner is
>> completely clueless about the selectivity of the condition
>>     "substring"(v.headervalue, 0,  255) ~~* '%<...@mail.gmail.com>%'
>> If it knew that that would match only one row, instead of several
>> thousand, it would likely pick a different plan.
>>
>> In recent versions of PG you could probably make a noticeable
>> improvement in this if you just dropped the substring() restriction
>> ... do you actually need that?  Alternatively, if you don't want to
>> change the query logic at all, I'd try making an index on
>> substring(v.headervalue, 0, 255).  I'm not expecting the query
>> to actually *use* the index, mind you.  But its existence will prompt
>> ANALYZE to collect stats on the expression's value, and that will
>> help the planner with estimating the ~~* condition.
>
> Well, that substring() and ILIKE combo looked suspicious to me,
> too.  However, there already was an index on
> substring(v.headervalue, 0, 255) but the fast query plan didn't seem
> to use it, it used a different index instead:
>
[...]
> Meanwhile, a mate of mine lurking on this list pointed out that
> reducing random_page_cost might help here and it did:
> random_page_cost of 2 made the fast query favourable.
>
> Can it mean that the default planner configuration slightly
> overfavours seq scans?
>

Funnily, after a few days of running with random_page_cost=2, exactly
the same query became slow again and I had to reduce random_page_cost
further to 1.5 to make it fast.  Can it be a sign of a problem in the
planner?

Thanks!

Yar