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.
