From: Tom Lane <tgl@sss.pgh.pa.us>
To: Murat Tasan <mmuurr@gmail.com>
Cc: Ants Aasma <ants@cybertec.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: does the query planner consider work_mem?
Date: Fri, 01 Jun 2012 11:15:09 -0400
Message-ID: <25585.1338563709@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+YV+HzqV8+3+9ZZspPnctBHnLRmWBb+3FKDASDuRsMZ9nab9w@mail.gmail.com>
References: <CA+YV+HxVLPGYRaN9HEfH7UDUDP40g4a=dKRC-NZF+i_a8oVGVg@mail.gmail.com>
<CA+CSw_uuyJAC7xejcoD6n1b8XF-TKw8KU6d95ssXKaBVQ1HL8A@mail.gmail.com>
<CA+YV+HzqV8+3+9ZZspPnctBHnLRmWBb+3FKDASDuRsMZ9nab9w@mail.gmail.com>
Murat Tasan <mmuurr@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mmuurr@gmail.com, ants@cybertec.at
Subject: Re: does the query planner consider work_mem?
In-Reply-To: <25585.1338563709@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox