Re: [HACKERS] Not enough memory for complex join

On Thu, 4 Mar 1999, Hannu Krosing wrote:
> >    I played with the query, and found I can remove (AND d.city_id = 2). The
> > query executes pretty fast (my database is small). I tried to get
> > d.city_id:
> > 
> > SELECT p.subsec_id, d.city_id
> >    FROM  positions p, central cn, shops sh, districts d
> >       WHERE cn.pos_id = p.pos_id
> >       AND   cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
> >       AND   cn.date_i >= current_date - '7 days'::timespan
> > 
> > but postgres eats all memory and dies :(((
> 
> What does EXPLAIN say ?

EXPLAIN SELECT p.subsec_id, d.city_id  FROM  positions p, central cn, shops sh, districts d     WHERE cn.pos_id =
p.pos_id    AND   cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id     AND   cn.date_i >= current_date - '7
days'::timespan
;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00 size=1 width=18) ->  Nested Loop  (cost=0.00 size=1 width=14)       ->  Merge Join  (cost=0.00
size=1width=10)             ->  Seq Scan  (cost=0.00 size=0 width=0)                   ->  Sort  (cost=0.00 size=0
width=0)                        ->  Seq Scan on districts d  (cost=0.00 size=0 width=4)             ->  Seq Scan
(cost=0.00size=0 width=0)                   ->  Sort  (cost=0.00 size=0 width=0)                         ->  Seq Scan
onshops sh  (cost=0.00 size=0 width=6)       ->  Seq Scan on central cn  (cost=0.00 size=0 width=4) ->  Seq Scan on
positionsp  (cost=0.00 size=0 width=4)
 

EXPLAIN


> --------------------
> Hannu
> 

Oleg.
----    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net          Programmers don't die, they
justGOSUB without RETURN.