Re: Is a better way to have the same result of this - Mailing list pgsql-performance

From Ron Johnson
Subject Re: Is a better way to have the same result of this
Date
Msg-id 1039066008.11433.3.camel@haggis
Whole thread Raw
In response to Is a better way to have the same result of this query?  (Vernon Wu <vernonw@gatewaytech.com>)
Responses Re: Is a better way to have the same result of this
List pgsql-performance
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)
>
> It seems take quite long to run this query. How to optimise the query?
>
> Thanks for your input.
>
> Vernon

What kind of indexes, if any, do you have on, and what is the
cardinality of account, block and preference?

What version of Postgres are you using?

How much shared memory and buffers are you using?

--
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "they love our milk and honey, but preach about another    |
|  way of living"                                            |
|    Merle Haggard, "The Fighting Side Of Me"                |
+------------------------------------------------------------+


pgsql-performance by date:

Previous
From: Vernon Wu
Date:
Subject: Is a better way to have the same result of this query?
Next
From: "john cartmell"
Date:
Subject: ORDER BY ... LIMIT.. performance