Hi All,
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.
Thanks,
Stalin
Env: Sol 10, Pg 827 64bit.