Re: Rapidly finding maximal rows

On Tue, Oct 11, 2011 at 3:16 AM, James Cranch <jdc41@cam.ac.uk> wrote:

>
> This is EXPLAIN ANALYZEd here:
>  http://explain.depesz.com/s/EiS

"Sort Method: external merge Disk: 35712kB"

>
> SOFTWARE AND HARDWARE
> =====================
>
> I'm running "PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC
> gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit". It's the standard installation
> from Debian stable (Squeeze), and I haven't messed around with it.
>
> My Linux kernel is 2.6.32-5-amd64.
>
> I have a desktop PC with a Intel Core i7 CPU and 6GB of RAM, and a single
> 640GB Hitachi HDT72106 disk. My root partition is less than 30% full.

Try setting work_mem to something larger, like 40MB to do that sort
step in memory, rather than spilling to disk. The usual caveats apply
though, like if you have many users/queries performing sorts or
aggregations, up to that amount of work_mem may be used at each step
potentially resulting in your system running out of memory/OOM etc.