Re: Slow query with a lot of data

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(at)houseofdesign(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>