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

Vernon Wu wrote:
>
> 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)

You might want to flatten this into more joins and less subqueries,
especially since you are using IN which is not very optimized:

SELECT p.userid, p.year, a.country, a.province, a.city
FROM profile p, account a, preference f, profile p1
WHERE
    f.userid = p.userid AND
    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
    p1.userid='Joe' AND
    2002-p1.year BETWEEN f.minage AND f.maxage

Also, I am not sure about the NOT IN. If you can rewrite it using EXISTS
try that, it might be faster.


> 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)

rows=1000 usually indicates you didn't vacuum analyze. Did you?

>   ->  Seq Scan on block  (cost=0.00..20.00 rows=995 width=0)

And to add to Vernons questions: if you are using PostgreSQL 7.2 or
later, please send us the EXPLAIN ANALYZE output.

Jochem