Re: Sort Method: external merge

Hi,

On 26 August 2011 00:14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> =?UTF-8?Q?Ondrej_Ivani=C4=8D?= <ondrej(dot)ivanic(at)gmail(dot)com> writes:
>> work_mem is set to 4 000 000 kb and I do not understand why few
>> queries (3 and 5) used disk and the rest fit were able to data into
>> memory.
>
> The on-disk representation of sort data is quite a bit more compact than
> the in-memory representation.  So where it says that 3.5GB of disk were
> needed, it's not a surprise that the memory requirement would have
> exceeded 4GB to do the sort in-memory.

I managed to do in memory shorting by setting work_mem to 9GB. Memory
usage peeked around 6.5GB. The idea behind this exercise was to see if
query could perform better:

- original query: around 8 hours
- parallelised query over 4 connections:
default work_mem (=256M): 110 minutes
work_mem = 4G: 99 minutes
work_mem = 9G: 97 minutes

parallelised query: original query is "group by" style query and one
of the group by attributes has around 40 unique values. I replaced
original query by 40 queries (with different value for that attribute)
and execute them over 4 connections.

> If you want to know what the conversion factor is for your particular
> dataset, try doing the query with small and large work_mem so you can
> see what is reported as the amount of space needed each way.

Looks like that disk representation is half of memory usage in my
case. Anyway, the test showed that work_mem is "irrelevant" in my case
(disk is fast SSD PCIE card)

Thanks,
--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)