Re: Preventing query from hogging server

"Matthew Nuzum" <matt(dot)followers(at)gmail(dot)com> writes:
> I believe there are about 40,000,000 rows, I expect there to be about
> 10,000,000 groups. PostgreSQL version is 7.3.2 and the sort_mem is at the
> default setting.

Okay. I doubt that the nearby suggestion to convert the min()s to
indexscans will help at all, given those numbers --- there aren't enough
rows per group to make it a win.

I think you've just gotta put up with the sorting required to bring the
groups together. LIMIT or subdividing the query will not make it
faster, because the sort step is the expensive part. You could probably
improve matters by increasing sort_mem as much as you can stand ---
maybe something like 10M to 100M (instead of the default 1M). Obviously
you don't want to make it a big fraction of your available RAM, or it
will hurt the concurrent processing, but on modern machines I would
think you could give this a few tens of MB without any problem. (Note
that you want to just SET sort_mem in this one session, not increase it
globally.)

I would strongly suggest doing the min and max calculations together:

select groupid, min(col), max(col) from ...

because if you do them in two separate queries 90% of the effort will be
duplicated.

regards, tom lane