Re: Memory usage on subselect

On 23 May 2004, at 19:32, Alexander M. Pravking wrote:
> BTW, after cancelling the original query postgres freed all the memory,
> and used ~7M again, so the leak was not "forever".
>

I have a similar problem with just one of my queries (although it isn't
a sub select):

SELECT
da_records.*
FROM
da_records, lu_dewey, da_dewey_list
WHERE
(da_records.RECORD_ID = da_dewey_list.RECORD_ID) AND
(lu_dewey.DEWEY_ID = da_dewey_list.DEWEY_ID) AND
(lu_dewey.DEWEY_HUNDREDS = 7) AND
(lu_dewey.DEWEY_TENS = 0) AND
(lu_dewey.DEWEY_ONES = 8) AND
(lu_dewey.DEWEY_LANG = 'en') AND
(lu_dewey.DEWEY_TYPE = 't') AND
(lu_dewey.DEWEY_ARCHIVE IS NOT TRUE) AND
(lu_dewey.dewey_point_ones IS NULL) AND
(lu_dewey.dewey_point_tens IS NULL) AND
(lu_dewey.dewey_point_hundreds IS NULL) AND
(lu_dewey.dewey_point_thousands IS NULL) AND
(lu_dewey.dewey_point_tenthousands IS NULL) AND
(lu_dewey.dewey_point_hundredthousands IS NULL)

EXPLAIN results:

Hash Join (cost=57.58..82.89 rows=25 width=661)
Hash Cond: ("outer".record_id = "inner".record_id)
-> Seq Scan on da_records (cost=0.00..20.00 rows=1000 width=649)
-> Hash (cost=57.56..57.56 rows=5 width=12)
-> Hash Join (cost=32.50..57.56 rows=5 width=12)
Hash Cond: ("outer".dewey_id = "inner".dewey_id)
-> Seq Scan on da_dewey_list (cost=0.00..20.00
rows=1000 width=8)
-> Hash (cost=32.50..32.50 rows=1 width=4)
-> Seq Scan on lu_dewey (cost=0.00..32.50 rows=1
width=4)
Filter: (((dewey_hundreds)::text =
'7'::text) AND ((dewey_tens)::text = '0'::text) AND ((dewey_ones)::text
= '8'::text) AND (dewey_lang = 'en'::bpchar) AND (dewey_type =
't'::bpchar) AND (dewey_archive IS NOT TRUE) AND (dewey_point_ones IS
NULL) AND (dewey_point_tens IS NULL) AND (dewey_point_hundreds IS NULL)
AND (dewey_point_thousands IS NULL) AND (dewey_point_tenthousands IS
NULL) AND (dewey_point_hundredthousands IS NULL))
(10 rows)

--
Dan Field <dof(at)llgc(dot)org(dot)uk> - Support Programmer: Cymru ar y we
cy_GB: http://www.cymruarywe.org
en_GB: http://www.walesontheweb.org