Re: Sort performance

Robert Haas <robertmhaas(at)gmail(dot)com> writes:

> On Thu, Jan 29, 2009 at 3:15 PM, Subbiah Stalin-XCGF84
> <SSubbiah(at)motorola(dot)com> wrote:
>>
>> i see the sort operation spilling to disk writing upto 430MB and then
>> return the first 500 rows. Our query is of the sort
>>
>> Now if set the work_mem to 500MB (i did this in a psql session without
>> making it global) and ran the same query. One would think the sort
>> operations would happen in memory and not spill to disk but i still see
>> 430MB written to disk however, the query complete time dropped down to
>> 351Secs. So work_mem did have an impact but wondering why its still writing
>> to disk when it can all do it memory.

The on-disk storage is more compact than the in-memory storage so you actually
need a larger value than the space reported for on-disk storage to avoid the
disk sort entirely. The accounting also isn't perfect; the on-disk sort still
uses some ram, for example.

> What happens if you set work_mem to something REALLY big, like 5GB?

Don't set it larger than the available RAM though -- or you'll quite possibly
get an out-of-error error.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!