Re: Performance problem with joined aggregate query

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