From: Scott Carey <scott@richrelevance.com>
To: Moritz Onken <onken@houseofdesign.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query with a lot of data
Date: Wed, 20 Aug 2008 09:01:07 -0700
Message-ID: <a1ec7d000808200901k44392979v98ae5ca83e8a0beb@mail.gmail.com> (raw)
In-Reply-To: <6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de>
References: <90AA1B69-4E9C-4710-9D58-F65620DDA022@houseofdesign.de>
<alpine.DEB.1.10.0808181657350.4454@aragorn.flymine.org>
<3663375B-7AC0-43F6-B6EC-D3EE572F3D34@houseofdesign.de>
<alpine.DEB.1.10.0808191202450.4454@aragorn.flymine.org>
<24E3FBFF-E0B8-435E-ACE1-652E93FE2213@houseofdesign.de>
<alpine.DEB.1.10.0808191311540.4454@aragorn.flymine.org>
<380CD097-4A33-4134-8C13-B9BF34EC6C63@houseofdesign.de>
<a1ec7d000808190749n43cdf29ap538c21b09cce1eac@mail.gmail.com>
<979A6EAC-24DC-4D92-A4B6-F8702221C5EC@houseofdesign.de>
<6F57A01F-F83F-477F-B86B-617D0F843FB1@houseofdesign.de>
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
>
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: scott@richrelevance.com, onken@houseofdesign.de
Subject: Re: Slow query with a lot of data
In-Reply-To: <a1ec7d000808200901k44392979v98ae5ca83e8a0beb@mail.gmail.com>
* 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