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
