Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
VACUUM
cwu=# SHOW work_mem;
 work_mem 
----------
 4MB
(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';
SET
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.

share|improve this question

migrated from stackoverflow.com Apr 27 '13 at 17:19

This question came from our site for professional and enthusiast programmers.

    
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 '13 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 '13 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 '13 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 '13 at 10:18

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
4  
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. –  Craig Ringer Apr 24 '13 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? –  Jakub Kania Apr 27 '13 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. –  Craig Ringer Apr 28 '13 at 7:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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