Re: Any better plan for this query?..

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
Cc: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Aidan Van Dyk <aidan(at)highrise(dot)ca>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Any better plan for this query?..
Date: 2009-05-12 11:35:32
Message-ID: 4A095F04.8090200@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Dimitri wrote:
> What I discovered so far with all your help:
> - the impact of a planner
> - the impact of the analyze target
> - the impact of prepare / execute
> - scalability limit on 32 cores

You've received good advice on how to minimize the impact of the first
three points, and using those techniques should bring a benefit. But I'm
pretty surprised by the bad scalability you're seeing and no-one seems
to have a good idea on where that limit is coming from. At a quick
glance, I don't see any inherent bottlenecks in the schema and workload.

If you could analyze where the bottleneck is with multiple cores, that
would be great. With something like oprofile, it should be possible to
figure out where the time is spent.

My first guess would be the WALInsertLock: writing to WAL is protected
by that and it an become a bottleneck with lots of small
UPDATE/DELETE/INSERT transactions. But a profile would be required to
verify that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Simon Riggs 2009-05-12 11:49:41 Re: Any better plan for this query?..
Previous Message Simon Riggs 2009-05-12 11:33:10 Re: Any better plan for this query?..