Skip site navigation (1) Skip section navigation (2)

Re: Sort performance

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Subbiah Stalin-XCGF84 <SSubbiah(at)motorola(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Sort performance
Date: 2009-01-29 23:21:17
Message-ID: 603c8f070901291521u16384932ifee8fd03fdd527bf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Jan 29, 2009 at 3:15 PM, Subbiah Stalin-XCGF84
<SSubbiah(at)motorola(dot)com> wrote:
> I'm in the process of tuning a query that does a sort on a huge dataset.
> With work_mem set to 2M, i see the sort operation spilling to disk writing
> upto 430MB and then return the first 500 rows. Our query is of the sort
>
> select co1, col2... from table where col1 like 'aa%' order col1 limit 500;
> It took 561Secs to complete. Looking at the execution plan 95% of the time
> is spent on sort vs seq scan on the table.
>
> 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.
>
> I appreciate if anyone can shed some light on this.

Can you send the EXPLAIN ANALYZE output?

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

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Subbiah Stalin-XCGF84Date: 2009-01-29 23:29:19
Subject: Re: Sort performance
Previous:From: Joshua D. DrakeDate: 2009-01-29 23:19:42
Subject: Re: Using multiple cores for index creation?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group