Re: PostgreSQL Query Speed Issues - Mailing list pgsql-novice

Hi Kevin,

Again, many thanks! The team is implementing your suggestions.

From a sysadmin perspective, something odd also seems to be the lack of
OS disk caching. After some very heavy use over many hours, 12 of the
16GB total RAM is still free rather then being allocated to disk buffering:

Mem:  16013788k total,  4013608k used, 12000180k free,   108108k buffers
Swap:  2097148k total,        0k used,  2097148k free,  3267868k cached

When a query hangs, we also see a core become and stay saturated:

PID     USER    PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27135 postgres  20   0 3510m 471m 315m R 100.0  3.0  71:06.82 postmaster

Without that core doing any disk I/O:

Cpu1  : 52.8%us, 47.2%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,
0.0%st

I get the feeling that we've missed a basic setting change that's
required to move from that default, low end laptop you described in an
earlier post to the enterprise environment we'd like to operate in. :)

Best regards,

-Tom

On 02/27/2013 10:44 AM, Kevin Grittner wrote:
> Joseph Pravato <joseph.pravato@nomagic.com> wrote:
>
>> This query that we have been talking about is just a sample that
>> we used to get a start on performance improvements. The original
>> performance related issue we had was with a large view that we
>> use for our customer & sales information that accesses 3
>> additional views and joins a total of 23 tables.
> Ah, well that gets us into a whole new ballgame.  With that many
> tables, planning time can become an issue in itself, so there are
> various things that PostgreSQL does to try to planning time from
> ballooning to a point where it takes longer than the time saved by
> plan improvement.  The first thing I would try is boosting these
> settings to more than the number of table references:
>
> from_collapse_limit
> join_collapse_limit
> geqo_threshold
>
> Try an EXPLAIN ANALYZE and see whether the "actual time" at the top
> level node of the plan looks good, and see how it compares to
> "Total runtime" at the bottom.  The difference is primarily
> planning time, so you can see how good a plan you got versus how
> expensive it was to find that plan.  If you see that there are good
> plans, but it is too expensive to find them, you might want to let
> the "genetic query optimizer" have a shot at planning, by adjusting
> the above values.  The concept of this alternative planner is that
> it tries to get a plan which is "good enough" with bounded planning
> time.
>
> See the docs for details.
>
>> Before the suggestions you gave it returned in 7 - 10 minutes and
>> now returns in less than 10 seconds. However, we have a copy of
>> our data on another database that runs in less than 0.5 seconds.
>> We think based on the previous messages in this thread that it is
>> still choosing a sub-optimal query plan for the views.
>>
>> This is the explain analyze for our customer data view.
>> http://pastebin.com/kSfb2dqy
>> Here is the output for the queries you provided.
>> http://pastebin.com/Yp80HCpe
> The plan seems to be reluctant to use index scans, which might be
> related to the ratio between these values:
>
> random_page_cost           | 1
> seq_page_cost              | 0.1
>
> Do you get a better plan if these are equal?  If random is only
> twice the sequential cost?
>
> The other possible issue is that depending on how the views are
> used, it sometimes creates an optimization barrier.  In general,
> the planner will see more options if the views are joined than if
> they are used in subqueries or CTEs.
>
> You might also want to try pasting your plan into:
>
> http://explain.depesz.com/
>
> This formats the plan and highlights portions you might want to pay
> special attention to.
>
> --
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>