X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 05694534A1 for ; Thu, 24 Mar 2005 20:02:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49220-10 for ; Thu, 24 Mar 2005 20:02:18 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B8A1D53494 for ; Thu, 24 Mar 2005 20:02:15 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2OK2Gxi005247; Thu, 24 Mar 2005 15:02:16 -0500 (EST) To: matt@followers.net Cc: pgsql-performance@postgresql.org Subject: Re: Preventing query from hogging server In-reply-to: <42431ac6.29dd6143.2b52.ffffbf08@mx.gmail.com> References: <42431ac6.29dd6143.2b52.ffffbf08@mx.gmail.com> Comments: In-reply-to "Matthew Nuzum" message dated "Thu, 24 Mar 2005 13:53:37 -0600" Date: Thu, 24 Mar 2005 15:02:15 -0500 Message-ID: <5246.1111694535@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/519 X-Sequence-Number: 11224 "Matthew Nuzum" 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