This is not quite my day with postgres. On my server machine with PosgreSQL 9.2.3 I have set work_mem to 4MB to avoid Sort Method: external merge Disk: 2072kB but it did not help:

cwu=# vacuum analyze web_city;
cwu=# SHOW work_mem;
(1 row)
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                          QUERY PLAN                                                          
 GroupAggregate  (cost=18304.35..20487.34 rows=95562 width=10) (actual time=1557.871..1809.029 rows=64459 loops=1)
   ->  Sort  (cost=18304.35..18633.84 rows=131796 width=10) (actual time=1557.856..1707.069 rows=131796 loops=1)
         Sort Key: ("left"((name)::text, 5))
         Sort Method: external merge  Disk: 2072kB
         ->  Seq Scan on web_city  (cost=0.00..4842.45 rows=131796 width=10) (actual time=1.050..174.907 rows=131796 loops=1)
 Total runtime: 1828.936 ms
(6 rows)

Setting work_mem to 8MB finally helps:

cwu=# SET work_mem = '8MB';
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                       QUERY PLAN                                                       
 HashAggregate  (cost=5501.43..6675.72 rows=93943 width=10) (actual time=207.628..244.667 rows=64459 loops=1)
   ->  Seq Scan on web_city  (cost=0.00..4842.45 rows=131796 width=10) (actual time=0.749..102.511 rows=131796 loops=1)
 Total runtime: 263.154 ms
(3 rows)

But why 4MB is not enough? In postgres wiki, there is this note:

if you see a line like "Sort Method: external merge Disk: 7526kB" in there, you'd know a work_mem of at least 8MB would really improve how fast that query executed, by sorting in RAM instead of swapping to disk.

So I assumed it will be the same in my case.

EDIT: If I do:

cwu=# create index name_left_prefix on web_city(left(name, 5));

then 4MB is finally enough. It seems that the index causes lower memory usage. If anyone would be that kind to explain all this behaviour I would be very grateful.

  • Some of the work_mem may already be used for other stuff. Have you tried setting it higher to see if the disk sort goes away (say e.g. 10MB)?
    – C. Ramseyer
    Apr 23, 2013 at 21:14
  • Yes it goes away. But in docs, there is "Sort Method: external merge Disk: 7526kB" in there, you'd know a work_mem of at least 8MB would really improve how fast that query executed, by sorting in RAM instead of swapping to disk." so I assumed it would be similar for my case.
    – clime
    Apr 23, 2013 at 21:16
  • BTW: the query makes no sense, why would you want to know the group counts without wanting to know to which groups they belong. In normal cases, Pg will generate a hash-aggregate (over a seq scan) plan, which makes perfect sense. Sorting or index would only make sense if the result set were too large to fit in (work)memory.
    – wildplasser
    Apr 24, 2013 at 10:00
  • @wildplasser: It is just a test query. In the end it will be something like: select count(*), (left(name,5)) as prefix from web_city GROUP BY prefix;
    – clime
    Apr 24, 2013 at 10:18

3 Answers 3


This is somewhat speculative but Depesz (Hubert Lubaczewski) has this to say on the subject:

You might wonder, though, why PostgreSQL switched to Disk, when it used only 448kB? After all, work_mem is 1MB. Answer is pretty simple – as I understand – disk is used when work_mem is not enough, so it means it's already been filled. So, sort with “Disk: 448kB" would mean that more or less whole work_mem has been used plus 448kB of disk.

So in your case the used work_mem might be in the 6 MB range. Also, try reset work_mem first, maybe there's stuff in there from a previous query.

  • 9
    Just to clarify, what RESET work_mem does is reset the value of the work_mem* *setting* to its default, undoing any prior SET work_mem. It does *not* in any sense "clear" or "reset" the contents of the memory allocated for work_mem; this memory is automatically discarded at the end of each statement anyway. Also: Pg isn't very smart about estimating how much work_mem` has already been allocated; you can't rely on work_mem actually capping the total working memory used by a query. Apr 24, 2013 at 0:08
  • @CraigRinger According to the docs work_mem is the memory per operation not per query. You mean it can exceed that value for a single operation? Apr 27, 2013 at 17:38
  • @JakubKania It can't exceed the memory set for a single operation, but you can get several times work_mem used in a query because of multiple memory-using operations. Apr 28, 2013 at 7:36
  • @JakubKania work_mem accounting is pretty loose. It is possible to exceed it even within one operation, because it often overlooks things like fragmentation that makes freed memory not actually reusable or returnable to the OS, and sometimes forgets to account for entire ancillary data structures. Also, some operations (not sort) can't spill to disk, so if it realizes it misestimated the size, it just has to press on regardless.
    – jjanes
    May 1, 2019 at 13:16

The reported amount reported by Sort Method: external merge Disk: 2072kB is not the amount quicksort would need. I ran into this issue and saw Sort Method: external merge Disk: 28408kB i started raising work_mem to see what happened and when I had raised it enough it reported Sort Method: quicksort Memory: 66629kB. So quicksort needs more then two times of what a disk merge needs.


You are in an anti-sweet spot here. Why do you care if it uses disk versus memory sort? If you just want the query's answer, you are paying too much attention to the plan details. While if you want to improve or micromanage the database software itself, then you are not paying enough attention to the plan details.

Sorting (as well as many other things) have improved quite a bit since 9.2.3. Any serious study of PostgreSQL should start with an upgrade. (But I see this was originally posted a very long time ago, I guess it got revived by Eelke--much of the info below still applies).

Note that when you increased the work_mem to 8MB, it switched to a HashAggregate, so it is no longer sorting at all, but rather is using an in-memory hash table. This is good for performance, but not so good for an academic understanding of how sort functions. If you want to disable the switch to HashAggregate for experimental purposes, you set enable_hashagg = off. You didn't show the EXPLAIN plan for when you had the functional index, but if you look at it you will probably see there is no sort at all. It gets the order from the index. So yes, a sort that does not exist does not consume memory.

But anyway, the data stored in temp files on disk is stored in a much more compact format than it is when stored in memory. One is optimized for serial access, one for random access. The in-memory data has accounting overhead and indirection arrays. Since the data fields you are sorting are so small, the size of the overhead is far higher than size of the data itself. How much disk a sort consumed is a lower bound, not an accurate predictor, of how much space it would need to sort in memory. The wiki section you quoted overlooks this subtly, perhaps it could be improved. Note that the wiki is not the docs. It is not vetted nearly as thoroughly as the docs are, and almost anyone can go change it.

  • This is a very good explanation!
    – ahron
    Feb 11 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.