Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 8048E13BD197 for ; Fri, 1 Jun 2012 12:15:26 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SaTZU-000528-6j for pgsql-performance@postgresql.org; Fri, 01 Jun 2012 15:15:25 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q51FF97Y025586; Fri, 1 Jun 2012 11:15:09 -0400 (EDT) To: Murat Tasan cc: Ants Aasma , pgsql-performance@postgresql.org Subject: Re: does the query planner consider work_mem? In-reply-to: References: Comments: In-reply-to Murat Tasan message dated "Wed, 30 May 2012 16:30:33 -0400" Date: Fri, 01 Jun 2012 11:15:09 -0400 Message-ID: <25585.1338563709@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201206/4 X-Sequence-Number: 47030 Murat Tasan writes: > p.s. on the 8.4 version EXPLAIN ANALYZE *still* tells me that an > external merge on disk is happening, despite my setting of work_mem to > a full 16 MB this time. When we have to push sort data to disk, it's written in a form that's noticeably more compact than what's used in-memory. So it can be expected that an in-memory sort will need significantly more memory than what an external merge reports using on-disk. I'd try cranking up work_mem to at least 10X the on-disk size if you want to be sure of seeing an in-memory sort. However, so far as I can see the sort is taking much less time than the scan and join steps anyway, so you're not likely to get much improvement this way. The unstable performance is a result of caching effects for the table data, not of how the sort is done. regards, tom lane