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

Поиск
Список
Период
Сортировка
От Jean-Luc Lachance
Тема Re: Is a better way to have the same result of this
Дата
Msg-id 3DEFACD6.A9573026@nsd.ca
обсуждение исходный текст
Ответ на Re: Is a better way to have the same result of this  (Vernon Wu <vernonw@gatewaytech.com>)
Ответы Re: Is a better way to have the same result of this  (Vernon Wu <vernonw@gatewaytech.com>)
Список pgsql-performance
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)
> >>

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tomasz Myrta
Дата:
Сообщение: Re: Is a better way to have the same result of this
Следующее
От: Andrew Sullivan
Дата:
Сообщение: Re: Is a better way to have the same result of this