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=1 width=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.00 size=0 width=0)
                    ->  Sort  (cost=0.00 size=0 width=0)
                          ->  Seq Scan on shops sh  (cost=0.00 size=0 width=6)
        ->  Seq Scan on central cn  (cost=0.00 size=0 width=4)
  ->  Seq Scan on positions p  (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 just GOSUB without RETURN.