Re: big distinct clause vs. group by

On 23 April 2011 21:34, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Apr 18, 2011, at 1:13 PM, Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> wrote:
> > Hi Robert,
> >
> > thanks for your answer.
> > the aggregate function I was talking about is the function I need to use
> for the non-group by columns like min() in my example.
> > There are of course several function to choose from, and I wanted to know
> which causes as less as possible resources.
>
> Oh, I see. min() is probably as good as anything. You could also create a
> custom aggregate that just always returns its first input. I've occasionally
> wished we had such a thing as a built-in.
>
yes. something like a first match without bothering about alle the rows
coming after - especially without sorting everything for throwing them away
finally. I'll definitely check this out.

>
> Another option is to try to rewrite the query with a subselect so that you
> do the aggregation first and then add the extra columns by joining against
> the output of the aggregate. If this can be done without joining the same
> table twice, it's often much faster, but it isn't always possible. :-(
>
Yes, abut I'm talking about big resultset on machines with already 140GB
RAM. If I start joining these afterwards this gets too expensive. I tried it
already. But thanks anyway. Often small hint helps you a lot.

Best Regards and happy Easter.
Uwe

>
> ...Robert