Thread

1. Re: [HACKERS] Problem with complex query

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-02-24T15:25:54Z

   Oleg Broytmann <phd@sun.med.ru> writes:
   > SELECT DISTINCT p.subsec_id
   >    FROM central cn, shops sh, districts d, positions p
   >       WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
   >       AND   d.city_id = %d AND cn.pos_id = p.pos_id
   >       AND   cn.date_i >= current_date - '7 days'::timespan
   > While running postgres slowly eats all swap space (30 Meg) and aborts:
   > pqReadData() -- backend closed the channel unexpectedly.
   >    Is it I just have not enough memory or bug?
   
   What version are you running?  Also, does it act the same if you try to
   EXPLAIN that same query?  If EXPLAIN fails then the problem is in the
   plan/optimize stage, not actual execution of the query.
   
   This kinda sounds like the optimizer problems that Bruce has fixed for
   6.5, but I don't recall anyone reporting serious problems with only
   4 tables in the query --- you had to get up to 7 or 8 or so before
   it really went nuts.
   
   			regards, tom lane
   
   

2. Re: [HACKERS] Problem with complex query

   Oleg Broytmann <phd@sun.med.ru> — 1999-02-24T15:36:05Z

   Hi!
   
   On Wed, 24 Feb 1999, Tom Lane wrote:
   > What version are you running?  Also, does it act the same if you try to
   
      6.4.2 on Sparc-solaris2.5.1
   
   > EXPLAIN that same query?  If EXPLAIN fails then the problem is in the
   > plan/optimize stage, not actual execution of the query.
   
      EXPLAIN works fine:
   
   EXPLAIN SELECT DISTINCT p.subsec_id
      FROM central cn, shops sh, districts d, positions p
         WHERE cn.shop_id = sh.shop_id AND sh.distr_id = d.distr_id
         AND   d.city_id = 2 AND cn.pos_id = p.pos_id
         AND   cn.date_i >= current_date - '7 days'::timespan
   ;
   NOTICE:  QUERY PLAN:
   
   Unique  (cost=0.00 size=0 width=0)
     ->  Sort  (cost=0.00 size=0 width=0)
           ->  Nested Loop  (cost=0.00 size=1 width=16)
                 ->  Nested Loop  (cost=0.00 size=1 width=12)
                       ->  Merge Join  (cost=0.00 size=1 width=8)
                             ->  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=2)
                             ->  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
   
   Oleg.
   ---- 
       Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
              Programmers don't die, they just GOSUB without RETURN.