Re: Is a better way to have the same result of this

It is now common knowledge that the IN clause should be rewriten as an
EXISTS.

SELECT p.userid, p.year, a.country, a.province, a.city
FROM profile p, account a
WHERE p.userid=a.userid AND
  (p.year BETWEEN 1961 AND 1976) AND
  a.country='CA' AND
  a.province='BC' AND
  p.gender='f' AND
  NOT EXISTS ( SELECT 1 FROM block b WHERE b.personid='Joe' AND p.userid
= b.userid) AND
  block.userid IS NOT NULL AND
  EXISTS ( SELECT 1 FROM preference f, profile p1
    WHERE p1.userid='Joe' AND p.userid = f.userif AND
      2002-p1.year BETWEEN f.minage AND f.maxage);



Vernon Wu wrote:
>
> Ron,
>
> The gender is indexed. Each user has account and preference, but not necessary block.
>
> I am currently seeking for query optimisation, not system configuration optimisation
>
> 12/4/2002 9:26:48 PM, Ron Johnson <ron.l.johnson@cox.net> wrote:
>
> >On Wed, 2002-12-04 at 18:26, Vernon Wu wrote:
> >> I have the following query:
> >>
> >> SELECT p.userid, p.year, a.country, a.province, a.city
> >> FROM profile p, account a
> >> WHERE p.userid=a.userid AND
> >>      (p.year BETWEEN 1961 AND 1976) AND
> >>      a.country='CA' AND
> >>      a.province='BC' AND
> >>      p.gender='f' AND
> >>      p.userid NOT IN (SELECT b.userid FROM block b WHERE b.personid='Joe') AND
> >>      block.userid IS NOT NULL AND
> >>      p.userid IN
> >>      (SELECT f.userid FROM preference f, profile p1 WHERE p1.userid='Joe' AND 2002-p1.year BETWEEN
> >>      f.minage AND f.maxage)
> >>
> >> In plain English, it is that
> >>
> >> Joe finds females between the ages in the location who is not in the block table, while Joe's age is between what
> they
> >> prefer.
> >>
> >> The query plan is the followings:
> >>
> >> Nested Loop  (cost=0.00..127.12 rows=995 width=894)
> >>   ->  Nested Loop  (cost=0.00..97.17 rows=1 width=894)
> >>         ->  Seq Scan on account a  (cost=0.00..25.00 rows=1 width=289)
> >>         ->  Index Scan using pk_profile on profile p  (cost=0.00..72.16 rows=1 width=605)
> >>               SubPlan
> >>                 ->  Materialize  (cost=22.50..22.50 rows=5 width=55)
> >>                       ->  Seq Scan on block b  (cost=0.00..22.50 rows=5 width=55
> >> )
> >>                 ->  Materialize  (cost=44.82..44.82 rows=111 width=89)
> >>                       ->  Nested Loop  (cost=0.00..44.82 rows=111 width=89)
> >>                             ->  Index Scan using pk_profile on profile p1  (cost=0.00..4.82 rows=1 width=12)
> >>                             ->  Seq Scan on preference f  (cost=0.00..20.00 rows=1000 width=77)
> >>   ->  Seq Scan on block  (cost=0.00..20.00 rows=995 width=0)
> >>