From: Anssi Kääriäinen <anssi.kaariainen@thl.fi>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Performance problem with joined aggregate query
Date: Thu, 16 Sep 2010 08:51:19 +0300
Message-ID: <4C91B057.2010106@thl.fi> (raw)
In-Reply-To: <AANLkTinQRvZ=m0uusv4qcvgVe6ToVWumMt8bGyBuq3i0@mail.gmail.com>
References: <4C906706.40108@thl.fi>
<AANLkTinQRvZ=m0uusv4qcvgVe6ToVWumMt8bGyBuq3i0@mail.gmail.com>
On 09/16/2010 01:25 AM, Merlin Moncure wrote:
> Take a look at this, and the responses. Is it the same case?:
> http://www.mail-archive.com/pgsql-performance@postgresql.org/msg21756.html
>
> merlin
>
Yes, looks like this is the same case. This makes it hard to use views
having group by in them, as the whole group by part will always be
executed. Back to planning board then...
I guess my possibilities for pivot views are:
- crosstab: Will make statistics go "bad", that is, the crosstab query
will always seem to return static number of rows. This can cause
problems in complex queries using the view. IIRC performance is
a bit worse than pivot by group by.
- left joins: if joining the same table 20 times, there will be some
planner overhead. Maybe the best way for my usage case. Also about
2x slower than pivot using group by.
- subselect each of the columns: way worse performance: for my use
case, each added column adds about 50ms to run time, so for 20
columns this will take 1 second. The group by pivot query runs in
250ms.
Any other ideas?
- Anssi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anssi.kaariainen@thl.fi, mmoncure@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Performance problem with joined aggregate query
In-Reply-To: <4C91B057.2010106@thl.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox