Re: Performance problem with joined aggregate query

On Thu, Sep 16, 2010 at 1:51 AM, Anssi Kääriäinen
<anssi(dot)kaariainen(at)thl(dot)fi> wrote:
> 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?

yes. specifically, if you are targeting the aggregation towards an
array you have another option:

analyze select * from test1 inner join (select array_agg(value), id
from test2 group by id) t on test1.id = t.id where test1.other_id = 10001;

can be replaced w/

select *, array(select value from test2 where test2.id=test1.id) from
test1 where test1.other_id = 10001;

This (array vs array_agg) will give you faster performance than
aggregation so is better preferred unless you need to group for other
purposes than relating. I think the join if it could match up over
the group by key can give theoretically better plans but this should
be good enough especially if you aren't pulling a large amount of data
from the outer table.

merlin