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

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)
>>
>> 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"                |
>+------------------------------------------------------------+
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>