From: | "Subbiah Stalin-XCGF84" <SSubbiah(at)Motorola(dot)com> |
---|---|
To: | "Gregory Stark" <stark(at)enterprisedb(dot)com>, "Robert Haas" <robertmhaas(at)gmail(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Sort performance |
Date: | 2009-01-29 23:58:04 |
Message-ID: | BF8D37611DA14544B3A47B8FF0559446030BFD3D@ct11exm61.ds.mot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)stark(at)enterprisedb(dot)com] On Behalf Of
Gregory Stark
Sent: Thursday, January 29, 2009 3:36 PM
To: Robert Haas
Cc: Subbiah Stalin-XCGF84; pgsql-performance(at)postgresql(dot)org
Subject: 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!
From | Date | Subject | |
---|---|---|---|
Next Message | henk de wit | 2009-01-30 00:02:55 | Re: Using multiple cores for index creation? |
Previous Message | Gregory Stark | 2009-01-29 23:35:44 | Re: Sort performance |