Re: bad planning with 75% effective_cache_size

[Istvan Endredy <istvan.endredy@xxxxxxxxx>]

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.Grittner@xxxxxxxxxxxx>

Istvan Endredy <istvan.endredy@xxxxxxxxx> 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