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

Re: bad planning with 75% effective_cache_size

From: Istvan Endredy <istvan(dot)endredy(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, Ants Aasma <ants(at)cybertec(dot)at>, tivv00(at)gmail(dot)com
Subject: Re: bad planning with 75% effective_cache_size
Date: 2012-04-19 14:15:09
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hi everybody,

thanks for the so many responses. :)

> On Thu, Apr 19, 2012 at 3:44 AM, Josh Berkus <josh(at)agliodbs(dot)com>
>>> 7500ms
>> This plan seems very odd -- doing individual index lookups on 2.8m rows
>> is not standard planner behavior.  Can you confirm that all of your
>> other query cost parameters are the defaults?

Josh: i confirm the non-default values:
i've ran this query:
its result:
"version";"PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by
gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"
"log_line_prefix";"%t %u(at)%h %d %p %i "

> I'm not sure what to do about unique node overestimation, but I think
> it could be coaxed to be less optimistic about the limit by adding an
> optimization barrier and some selectivity decreasing clauses between
> the limit and the rest of the query:
> select * from (
>    select distinct product_code from product p_
>    inner join product_parent par_ on
>    where par_.parent_name like 'aa%'
>    offset 0 -- optimization barrier
> ) as x
> where product_code = product_code -- reduce selectivity estimate by 200x
> limit 2;

Its planning:

> How about
> with par_ as (select * from product_parent where parent_name like 'aa%' )
> select distinct product_code from product p_
> inner join par_ on
> limit 2

Its planning:

All suggestions are welcome,

In response to

pgsql-performance by date

Next:From: Jan NielsenDate: 2012-04-24 02:56:47
Subject: Configuration Recommendations
Previous:From: Ants AasmaDate: 2012-04-19 10:32:50
Subject: Re: bad planning with 75% effective_cache_size

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