Is a better way to have the same result of this query?

Поиск
Список
Период
Сортировка
От Vernon Wu
Тема Is a better way to have the same result of this query?
Дата
Msg-id C0KJ8787HBOMTP74FBGCRN1UNLVS6ZTR.3dee9d26@kimiko
обсуждение исходный текст
Ответы Re: Is a better way to have the same result of this  (Ron Johnson <ron.l.johnson@cox.net>)
Re: Is a better way to have the same result of this query?  (Jochem van Dieten <jochemd@oli.tudelft.nl>)
Список pgsql-performance
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)

It seems take quite long to run this query. How to optimise the query?

Thanks for your input.

Vernon




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

Предыдущее
От: "scott.marlowe"
Дата:
Сообщение: Re: Low Budget Performance, Part 2
Следующее
От: Ron Johnson
Дата:
Сообщение: Re: Is a better way to have the same result of this