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.