Re: Slow query with a lot of data

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