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
