Skip site navigation (1) Skip section navigation (2)

Re: Memory usage on subselect

From: Dan Field <dof(at)llgc(dot)org(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Memory usage on subselect
Date: 2004-05-24 08:53:24
Message-ID: D11F1F08-AD5F-11D8-9250-000A958E367A@llgc.org.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-05-24 13:37:37
Subject: Re: Memory usage on subselect
Previous:From: Alexander M. PravkingDate: 2004-05-23 18:32:49
Subject: Re: Memory usage on subselect

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group