Re: How to make PG use work_mem?

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