A few weeks ago I reported a problem I was having with front end
memory consumption in SELECT, and Tom's suggestion about using a
cursor solved that problem very nicely at the front end.
However, my front and back end processes are running on the same
server, and I have just noticed that the back end process swells to
the total size of all the tables involved in my query for the
duration of the SELECT (or is it the duration of the declared cursor
now?).
To summarise the situation:
- back end processes on my system are usually about 3M
(PostgreSQL 6.5.1, Debian slink, Intel)
- I am attempting a join on 9 tables, using a cursor to fetch the
selected data
- I am only selecting a tiny fraction of the total data
- I am running in serializable mode if it matters
- the files used to store my tables (including associated keys
and indexes etc) take just under 17M on disk
- the back end process grows to over 20M while processing my query
This is a serious concern, because 17M is just some sample data, and
will eventually be much larger.
Any hints for ways of limiting back end memory consumption (hopefully
to something constant rather than something proportional to the
tables sizes)?
Doug.
