title: Configuration parameter work_mem in PostgreSQL on Linux
body: <p>I have to optimize queries by tuning basic PostgreSQL server configuration parameters. In documentation I've came across the <code>work_mem</code> parameter. Then I checked how changing this parameter would influence performance of my query (using sort). I measured query execution time with various <code>work_mem</code> settings and was very disappointed.</p>
  
  <p>The table on which I perform my query contains 10,000,000 rows and there are 430 MB of data to sort. (<code>Sort Method:  external merge  Disk: 430112kB</code>).</p>
  
  <p>With <code>work_mem = 1MB</code>, <code>EXPLAIN</code> output is:</p>
  
  <pre><code>Total runtime: 29950.571 ms (sort takes about 19300 ms).
  Sort  (cost=4032588.78..4082588.66 rows=19999954 width=8) 
  (actual time=22577.149..26424.951 rows=20000000 loops=1)
                   Sort Key: "*SELECT* 1".n
                   Sort Method:  external merge  Disk: 430104kB
  </code></pre>
  
  <p>With <code>work_mem = 5MB</code>:</p>
  
  <pre><code>Total runtime: 36282.729 ms (sort: 25400 ms).
  Sort  (cost=3485713.78..3535713.66 rows=19999954 width=8) 
        (actual time=25062.383..33246.561 rows=20000000 loops=1)
        Sort Key: "*SELECT* 1".n
        Sort Method:  external merge  Disk: 430104kB
  </code></pre>
  
  <p>With <code>work_mem = 64MB</code>:</p>
  
  <pre><code>Total runtime: 42566.538 ms (sort: 31000 ms).
  Sort  (cost=3212276.28..3262276.16 rows=19999954 width=8) 
  (actual time=28599.611..39454.279 rows=20000000 loops=1)
                   Sort Key: "*SELECT* 1".n
                   Sort Method:  external merge  Disk: 430104kB
  </code></pre>
  
  <p>Can anyone explain why performance gets worse? Or suggest any other methods to makes queries execution faster by changing server parameters?</p>
  
  <p>My query (I know it's not optimal, but I have to benchmark this kind of query):</p>
  
  <pre><code>SELECT n
  FROM   (
      SELECT n + 1 AS n FROM table_name
      EXCEPT
      SELECT n FROM table_name) AS q1
  ORDER BY n DESC;
  </code></pre>
  
  <p>Full execution plan:</p>
  
  <pre><code>Sort  (cost=5805421.81..5830421.75 rows=9999977 width=8) (actual time=30405.682..30405.682 rows=1 loops=1)
  Sort Key: q1.n
  Sort Method:  quicksort  Memory: 25kB
  -&gt;  Subquery Scan q1  (cost=4032588.78..4232588.32 rows=9999977 width=8) (actual time=30405.636..30405.637 rows=1 loops=1)
      -&gt;  SetOp Except  (cost=4032588.78..4132588.55 rows=9999977 width=8) (actual time=30405.634..30405.634 rows=1 loops=1)
             -&gt;  Sort  (cost=4032588.78..4082588.66 rows=19999954 width=8) (actual time=23046.478..27733.020 rows=20000000 loops=1)
                   Sort Key: "*SELECT* 1".n
                   Sort Method:  external merge  Disk: 430104kB
                   -&gt;  Append  (cost=0.00..513495.02 rows=19999954 width=8) (actual time=0.040..8191.185 rows=20000000 loops=1)
                         -&gt;  Subquery Scan "*SELECT* 1"  (cost=0.00..269247.48 rows=9999977 width=8) (actual time=0.039..3651.506 rows=10000000 loops=1)
                               -&gt;  Seq Scan on table_name  (cost=0.00..169247.71 rows=9999977 width=8) (actual time=0.038..2258.323 rows=10000000 loops=1)
                         -&gt;  Subquery Scan "*SELECT* 2"  (cost=0.00..244247.54 rows=9999977 width=8) (actual time=0.008..2697.546 rows=10000000 loops=1)
                               -&gt;  Seq Scan on table_name  (cost=0.00..144247.77 rows=9999977 width=8) (actual time=0.006..1079.561 rows=10000000 loops=1)
  Total runtime: 30496.100 ms
  </code></pre>