Re: Is a better way to have the same result of this - Mailing list pgsql-performance
From | Jean-Luc Lachance |
---|---|
Subject | Re: Is a better way to have the same result of this |
Date | |
Msg-id | 3DEFBF5C.656EADA2@nsd.ca Whole thread Raw |
In response to | Re: Is a better way to have the same result of this (Vernon Wu <vernonw@gatewaytech.com>) |
Responses |
Re: Is a better way to have the same result of this
|
List | pgsql-performance |
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@nsd.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.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) > >> >> > > > >---------------------------(end of broadcast)--------------------------- > >TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/users-lounge/docs/faq.html > >
pgsql-performance by date: