Re: query memory consumption

On Mon, 21 Sep 2009, Alan McKay wrote:
> We have explain and analyze which tell us about the cost of a query
> time-wise, but what does one use to determine (and trace / predict?)
> memory consumption?

In Postgres, memory consumption for all operations is generally capped at
the value of work_mem. However, a given query can consist of more than one
operation. Generally, only heavy things like sorts and hashes consume
work_mem, so it should be possible to look at the explain to count those,
multiply by work_mem, and get the maximum amount of RAM that the query can
use.

However, sometimes a query will not fit neatly into work_mem. At this
point, Postgres will write the data to temporary files on disc. It is
harder to predict what size those will be. However, EXPLAIN ANALYSE will
sometimes give you a figure of how big a sort was for example.

Matthew

--
 Reality is that which, when you stop believing in it, doesn't go away.
                                              -- Philip K. Dick