Thread: How to make PG use work_mem?
Hi,
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?
The query runs on a streaming replica if that matters.
Torsten
=?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
On 11/03/14 14:36, Tom Lane wrote:
> Perhaps you fat-fingered the SET somehow?
I just repeated it:
# select * from pg_settings where name='work_mem';
-[ RECORD 1 ]--------------------------------
name | work_mem
setting | 52428800
unit | kB
...
# explain (analyze,buffers)
select distinct(account_id, purchase_time::date) from fmb;
QUERY PLAN
-----------------------------------------------------------------------------
Unique (cost=13666972.01..14042722.46 rows=12894641 width=16)
(actual time=1000989.364..1058273.210 rows=2200442 loops=1)
Buffers: shared hit=1570088 read=1326647,
temp read=389842 written=389842
-> Sort (cost=13666972.01..13854847.24 rows=75150089 width=16)
(actual time=1000989.362..1035694.670 rows=74196802 loops=1)
Sort Key: (ROW(account_id, (purchase_time)::date))
Sort Method: external merge Disk: 3118720kB
Buffers: shared hit=1570088 read=1326647,
temp read=389842 written=389842
-> Seq Scan on fmb
(cost=0.00..3836111.11 rows=75150089 width=16)
(actual time=0.021..35520.901 rows=74196802 loops=1)
Buffers: shared hit=1570088 read=1326647
Total runtime: 1059324.646 ms
# show work_mem;
work_mem
----------
50GB
This is 9.3.3 from the pgdg debian repository.
Torsten
=?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.net> writes:
> On 11/03/14 14:36, Tom Lane wrote:
>> Perhaps you fat-fingered the SET somehow?
> I just repeated it:
[ thinks for awhile... ] Oh, I know what's happening: your sort is so
large that it's being constrained by the MaxAllocSize limit on the tuple
pointer array. This has been fixed in HEAD, but it's not yet in any
shipping release. According to the log entry for commit
263865a48973767ce8ed7b7788059a38a24a9f37, the previous limit on the number
of tuples that could be sorted in memory was INT_MAX/48 or about 44
million; I've not done the arithmetic to check that, but it seems about
right seeing that you're having trouble with 75 million.
regards, tom lane
On 11/03/14 16:03, Tom Lane wrote: > [ thinks for awhile... ] Oh, I know what's happening: your sort is so > large that it's being constrained by the MaxAllocSize limit on the tuple > pointer array. This has been fixed in HEAD, but it's not yet in any > shipping release. According to the log entry for commit > 263865a48973767ce8ed7b7788059a38a24a9f37, the previous limit on the number > of tuples that could be sorted in memory was INT_MAX/48 or about 44 > million; I've not done the arithmetic to check that, but it seems about > right seeing that you're having trouble with 75 million. Thanks, that makes sense. BTW, I solved my problem w/o that sort. I was just curious what happened here. Torsten