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

Re: Performance problem with joined aggregate query

From: Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Performance problem with joined aggregate query
Date: 2010-09-16 05:51:19
Message-ID: 4C91B057.2010106@thl.fi (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)postgresql(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Franck RoutierDate: 2010-09-16 08:23:47
Subject: Is disableing nested_loops a bad idea ?
Previous:From: Greg SmithDate: 2010-09-16 00:32:32
Subject: Re: locking issue on simple selects?

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