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@xxxxxxxxxxxxxxxx> wrote:
Am 19.08.2008 um 17:23 schrieb Moritz Onken:Hi,
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
--
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
