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@llgc.org.uk> - Support Programmer: Cymru ar y we
cy_GB: http://www.cymruarywe.org
en_GB: http://www.walesontheweb.org