Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 927F764FD12 for ; Wed, 20 Aug 2008 13:01:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 45469-10 for ; Wed, 20 Aug 2008 13:01:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.175]) by postgresql.org (Postfix) with ESMTP id 34E0064FD0B for ; Wed, 20 Aug 2008 13:01:08 -0300 (ADT) Received: by wf-out-1314.google.com with SMTP id 25so469736wfc.28 for ; Wed, 20 Aug 2008 09:01:07 -0700 (PDT) Received: by 10.143.31.11 with SMTP id i11mr76218wfj.328.1219248067485; Wed, 20 Aug 2008 09:01:07 -0700 (PDT) Received: by 10.143.39.19 with HTTP; Wed, 20 Aug 2008 09:01:07 -0700 (PDT) Message-ID: Date: Wed, 20 Aug 2008 09:01:07 -0700 From: "Scott Carey" To: "Moritz Onken" Subject: Re: Slow query with a lot of data Cc: pgsql-performance@postgresql.org In-Reply-To: <6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_30245_24727025.1219248067493" References: <90AA1B69-4E9C-4710-9D58-F65620DDA022@houseofdesign.de> <3663375B-7AC0-43F6-B6EC-D3EE572F3D34@houseofdesign.de> <24E3FBFF-E0B8-435E-ACE1-652E93FE2213@houseofdesign.de> <380CD097-4A33-4134-8C13-B9BF34EC6C63@houseofdesign.de> <979A6EAC-24DC-4D92-A4B6-F8702221C5EC@houseofdesign.de> <6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de> X-Google-Sender-Auth: dc9fe0e73cb9d7f2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/226 X-Sequence-Number: 31107 ------=_Part_30245_24727025.1219248067493 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline More work_mem will make the sort fit more in memory and less on disk, even with the same query plan. On Wed, Aug 20, 2008 at 12:54 AM, Moritz Onken wrote: > > Am 19.08.2008 um 17:23 schrieb Moritz Onken: > > >> Am 19.08.2008 um 16:49 schrieb Scott Carey: >> >> What is your work_mem set to? The default? >>> >>> Try increasing it significantly if you have the RAM and seeing if that >>> affects the explain plan. You may even want to set it to a number larger >>> than the RAM you have just to see what happens. In all honesty, it may be >>> faster to overflow to OS swap space than sort too many rows, but ONLY if it >>> changes the plan to a significantly more efficient one. >>> >>> Simply type >>> 'SET work_mem = '500MB'; >>> before running your explain. Set it to even more RAM if you have the >>> space for this experiment. >>> >>> In my experience the performance of aggregates on large tables is >>> significantly affected by work_mem and the optimizer will chosse poorly >>> without enough of it. It will rule out plans that may be fast enough when >>> overflowing to disk in preference to colossal sized sorts (which likely also >>> overflow to disk but take hours or days). >>> >> >> Thanks for that advice but the explain is not different :-( >> >> moritz >> >> -- >> > > Hi, > > I started the query with work_mem set to 3000MB. The explain output didn't > change but it runs now much faster (about 10 times). The swap isn't used. > How can you explain that? > > > moritz > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > ------=_Part_30245_24727025.1219248067493 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
More work_mem will make the sort fit more in memory and less on disk, even with the same query plan.


On Wed, Aug 20, 2008 at 12:54 AM, Moritz Onken <onken@houseofdesign.de> wrote:

Am 19.08.2008 um 17:23 schrieb Moritz Onken:



Am 19.08.2008 um 16:49 schrieb Scott Carey:

What is your work_mem set to?  The default?

Try increasing it significantly if you have the RAM and seeing if that affects the explain plan.  You may even want to set it to a number larger than the RAM you have just to see what happens.  In all honesty, it may be faster to overflow to OS swap space than sort too many rows, but ONLY if it changes the plan to a significantly more efficient one.

Simply type
'SET work_mem = '500MB';
before running your explain.  Set it to even more RAM if you have the space for this experiment.

In my experience the performance of aggregates on large tables is significantly affected by work_mem and the optimizer will chosse poorly without enough of it.  It will rule out plans that may be fast enough when overflowing to disk in preference to colossal sized sorts (which likely also overflow to disk but take hours or days).

Thanks for that advice but the explain is not different :-(

moritz

--

Hi,

I started the query with work_mem set to 3000MB. The explain output didn't change but it runs now much faster (about 10 times). The swap isn't used. How can you explain that?


moritz

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

------=_Part_30245_24727025.1219248067493--