Thread

1. RE: [HACKERS] Bug on complex subselect (was: Bug on complex join)

   Jackson, DeJuan <djackson@cpsgroup.com> — 1999-03-11T18:00:32Z

   > Hello!
   > 
   >    Vadim already gave the idea to use EXISTS. Will try it.
   >    Thanks to all who replied!
   > 
   > On Wed, 10 Mar 1999, Jackson, DeJuan wrote:
   > > Try your query this way:
   > >  SELECT DISTINCT subsec_id
   > >    FROM positions p
   > >   WHERE EXISTS(SELECT 1
   > >                  FROM central c, shops s, districts d
   > >                 WHERE p.pos_id = c.pos_id AND 
   > >                       c.shop_id = s.shop_id AND
   > >                       s.distr_id = d.distr_id AND
   > >                       d.city_id = 2);
   > 
   > > Make sure you have indexes on pos_id, shop_id, distr_id, 
   > and city_id.
   > 
   >    All these are primary keys in corresponding tables, and hence have
   > UNIQUE indicies. Is it enough?
   > 
   > Oleg.
   You should have indexes on both the primary and the referenced table.
   (i.e. positions.pos_id and central.pos_id)  It gives PostgreSQL more
   options on which join methods to use while still having an index to
   reference.
   
   	-DEJ
   
   

2. RE: [HACKERS] Bug on complex subselect (was: Bug on complex join)

   Oleg Broytmann <phd@sun.med.ru> — 1999-03-11T18:00:54Z

   Hi!
   
   On Thu, 11 Mar 1999, Jackson, DeJuan wrote:
   > > > Make sure you have indexes on pos_id, shop_id, distr_id, 
   > > and city_id.
   > > 
   > >    All these are primary keys in corresponding tables, and hence have
   > > UNIQUE indicies. Is it enough?
   > > 
   > > Oleg.
   > You should have indexes on both the primary and the referenced table.
   > (i.e. positions.pos_id and central.pos_id)  It gives PostgreSQL more
   > options on which join methods to use while still having an index to
   > reference.
   
      Understand.
      Thank you.
   
   > 
   > 	-DEJ
   > 
   
   Oleg.
   ---- 
       Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
              Programmers don't die, they just GOSUB without RETURN.