Re: How to make PG use work_mem? - Mailing list pgsql-general

=?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.net> writes:
> I have a query that involves an external sort:

>    ->  Sort  (cost=13662680.01..13850498.48 rows=75127389 width=16)
>              (actual time=980098.397..1021411.862 rows=74181544 loops=1)
>          Sort Key: (ROW(account_id, (purchase_time)::date))
>          Sort Method: external merge  Disk: 3118088kB
>          Buffers: shared hit=1568637 read=1327223,
>                   temp read=389763 written=389763

> What puzzles me is that this happens even when I set work_mem to 50GB in
> the session.

> Why does it still use the external merge?

Seems odd.  I wouldn't have been surprised if you'd said it didn't do it
at work_mem = 5GB.  The internal memory requirement can be noticeably
larger than the space required on-disk, mainly because we go to some
lengths to minimize the size of sort tuples when writing them out, but
not if they stay in memory.  But a difference of more than maybe 2X to
3X from that effect would be surprising.

Perhaps you fat-fingered the SET somehow?

            regards, tom lane