Re: Sort performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks Greg. You were right. If I set my sort_mem to 1G (yes I have
loads of memory, only for testing purpose), then I don't see any thing
written to disk. So in-memory require more memory than reported on-disk
storage.

Stalin  

-----Original Message-----
From: Greg Stark [mailto:greg.stark@xxxxxxxxxxxxxxxx] On Behalf Of
Gregory Stark
Sent: Thursday, January 29, 2009 3:36 PM
To: Robert Haas
Cc: Subbiah Stalin-XCGF84; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Sort performance

Robert Haas <robertmhaas@xxxxxxxxx> writes:

> On Thu, Jan 29, 2009 at 3:15 PM, Subbiah Stalin-XCGF84 
> <SSubbiah@xxxxxxxxxxxx> 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!

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux