From: Tom Lane <tgl@sss.pgh.pa.us>
To: matt@followers.net
Cc: pgsql-performance@postgresql.org
Subject: Re: Preventing query from hogging server
Date: Thu, 24 Mar 2005 15:02:15 -0500
Message-ID: <5246.1111694535@sss.pgh.pa.us> (raw)
In-Reply-To: <42431ac6.29dd6143.2b52.ffffbf08@mx.gmail.com>
References: <42431ac6.29dd6143.2b52.ffffbf08@mx.gmail.com>
"Matthew Nuzum" <matt.followers@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, matt@followers.net
Subject: Re: Preventing query from hogging server
In-Reply-To: <5246.1111694535@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox