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

> 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

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
justGOSUB without RETURN.