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