Re: bad planning with 75% effective_cache_size

Hi Kevin,

thanks for the suggestion. It was my 1st task to try this after Easter. :)

Sorry to say this parameter doesn't help:

bad planning:
set cpu_tuple_cost = '0.05';
set effective_cache_size to '6GB';
1622ms
http://explain.depesz.com/s/vuO

or
set cpu_tuple_cost = '0.01';
set effective_cache_size to '6GB';
1634ms
http://explain.depesz.com/s/YqS

good planning:
set effective_cache_size to '32MB';
set cpu_tuple_cost = '0.05';
22ms
http://explain.depesz.com/s/521

or
set effective_cache_size to '32MB';
set cpu_tuple_cost = '0.01';
12ms
http://explain.depesz.com/s/Ypc

this was the query:
select distinct product_code from product p_
inner join product_parent par_ on p_.parent_id=par_.id
where par_.parent_name like 'aa%' limit 2

Any idea?
Thanks in advance,
Istvan

2012/4/5 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>

> Istvan Endredy <istvan(dot)endredy(at)gmail(dot)com> wrote:
>
> > i've ran into a planning problem.
>
> > If effective_cache_size has a greater value (6GB), this select has
> > a bad planning and long query time (2000ms):
>
> Could you try that configuration with one change and let us know how
> it goes?:
>
> set cpu_tuple_cost = '0.05';
>
> I've seen an awful lot of queries benefit from a higher value for
> that setting, and I'm starting to think a change to that default is
> in order.
>
> -Kevin
>