On Thu, Jan 29, 2009 at 3:15 PM, Subbiah Stalin-XCGF84 <SSubbiah@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
