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

Good for you! Too bad the parser does not know about it...

Vernon Wu wrote:
>
> I just learnt the "common knowledge" about four hourse ago. That does help to improve the performance indeed
> according to the explain command.
>
> 12/5/2002 11:45:26 AM, Jean-Luc Lachance <jllachan(at)nsd(dot)ca> wrote:
>
> >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(dot)l(dot)johnson(at)cox(dot)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)
> >> >>
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/users-lounge/docs/faq.html
> >