Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5600B476476 for ; Mon, 13 Jan 2003 16:20:53 -0500 (EST) Received: from web14107.mail.yahoo.com (web14107.mail.yahoo.com [216.136.172.137]) by postgresql.org (Postfix) with SMTP id 7F0E447626A for ; Mon, 13 Jan 2003 16:20:52 -0500 (EST) Message-ID: <20030113212052.60493.qmail@web14107.mail.yahoo.com> Received: from [64.192.116.161] by web14107.mail.yahoo.com via HTTP; Mon, 13 Jan 2003 13:20:52 PST Date: Mon, 13 Jan 2003 13:20:52 -0800 (PST) From: Christopher Smith Subject: query speed joining tables To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-286406550-1042492852=:59259" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/187 X-Sequence-Number: 11052 --0-286406550-1042492852=:59259 Content-Type: text/plain; charset=us-ascii I have 4 tables that I need to query... 3 of the tables are links by the field userid. below are table sql fragments. as well as the query. The subquery causes very high CPU usages. It typically returns ~3000 matches. Is there another way to rewrite this? SELECT user_login.userid FROM user_login,user_details_p,user_match_details WHERE user_login.userid = user_details_p.userid AND user_details_p.userid = user_match_details.userid AND user_details_p.gender ='W' AND user_details_p.seekgender ='M' AND user_details_p.age >=18 AND user_details_p.age <=50 AND user_match_details.min_age <= 30 AND user_match_details.max_age >= 30 AND user_details_p.ethnictype = 'Caucasian (White)' AND strpos(user_match_details.ethnicity,'Asian') !=0 AND user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' ) order by user_login.last_login desc; Table "public.user_login" Column | Type | Modifiers ------------+--------------------------+--------------- userid | character varying(30) | not null password | character varying(30) | not null email | character varying(50) | not null last_login | timestamp with time zone | not null Indexes: user_login_pkey primary key btree (userid), Table "public.user_details_p" Column | Type | Modifiers -----------------+--------------------------+--------------- userid | character varying(30) | not null gender | character varying(1) | age | integer | height | character varying(10) | ethnictype | character varying(30) | education | character varying(30) | createdate | timestamp with time zone | default now() zipcode | character varying(5) | birthdate | date | default now() zodiac | character varying(40) | seekgender | character varying(2) | Indexes: user_details_p_pkey primary key btree (userid), user_details_p_age_idx btree (age), user_details_p_ethnic_idx btree (ethnictype), user_details_p_gender_idx btree (gender), user_details_p_last_login_idx btree (last_login), user_details_p_seekgender_idx btree (seekgender), user_details_p_state_idx btree (state) Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTI ON ON DELETE CASCADE Table "public.user_match_details" Column | Type | Modifiers ------------------+------------------------+----------- userid | character varying(30) | not null soughtmate | character varying(200) | ethnicity | character varying(200) | marital_status | character varying(200) | min_age | integer | max_age | integer | city | character varying(50) | state | character varying(2) | zipcode | integer | match_distance | integer | Indexes: user_match_details_pkey primary key btree (userid) Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table "public.zips_300" Column | Type | Modifiers -------------+----------------------+----------- origin | character varying(5) | destination | character varying(5) | Indexes: zips_300_origin_idx btree (origin) --------------------------------- Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now --0-286406550-1042492852=:59259 Content-Type: text/html; charset=us-ascii

I have 4 tables that I need to query... 3 of the tables are links by the field userid.

below are table sql fragments. as well as the query.  The subquery causes very high

CPU usages.  It typically returns ~3000 matches. Is there another way to rewrite this? 

SELECT user_login.userid FROM user_login,user_details_p,user_match_details
 WHERE user_login.userid = user_details_p.userid AND
user_details_p.userid = user_match_details.userid AND
user_details_p.gender ='W' AND
user_details_p.seekgender ='M' AND
user_details_p.age >=18 AND
user_details_p.age <=50 AND
user_match_details.min_age <= 30 AND
user_match_details.max_age >= 30 AND
user_details_p.ethnictype = 'Caucasian (White)' AND
strpos(user_match_details.ethnicity,'Asian') !=0 AND
user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' )
order by user_login.last_login desc;

 

               Table "public.user_login"
   Column   |           Type           |   Modifiers  
------------+--------------------------+---------------
 userid     | character varying(30)    | not null
 password   | character varying(30)    | not null
 email      | character varying(50)    | not null
 last_login | timestamp with time zone | not null
 Indexes: user_login_pkey primary key btree (userid),
        


               Table "public.user_details_p"
     Column      |           Type           |   Modifiers  
-----------------+--------------------------+---------------
 userid          | character varying(30)    | not null
 gender          | character varying(1)     |
 age             | integer                  |
 height          | character varying(10)    |
 ethnicty pe      | character varying(30)    |
 education       | character varying(30)    |
 createdate      | timestamp with time zone | default now()
 zipcode         | character varying(5)     |
 birthdate       | date                     | default now()
 zodiac          | character varying(40)    |
 seekgender      | character varying(2)     |
Indexes: user_details_p_pkey primary key btree (userid),
         user_details_p_age_idx btree (age),
      &nb sp;  user_details_p_ethnic_idx btree (ethnictype),
         user_details_p_gender_idx btree (gender),
         user_details_p_last_login_idx btree (last_login),
         user_details_p_seekgender_idx btree (seekgender),
         user_details_p_state_idx btree (state)
Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTI
ON ON DELETE CASCADE


           Table "public.user_match_details"
      Column      |          Type          | Modifiers
------------------+------------------------+-----------
 userid           | character varying(30)  | not null
 soughtmate       | character varying(200) |
 ethnicity        | character varying(200) |
 marital_status   | character varying(200) |
 min_age          | integer                |
 max_age          | integer   &nbs p;            |
 city             | character varying(50)  |
 state            | character varying(2)   |
 zipcode          | integer                |
 match_distance   | integer                |
Indexes: user_match_details_pkey primary key btree (userid)
Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE

            Table "public.zips_300"
   Column    |         Type         | Modifiers
-------------+----------------------+-----------
 origin      | character varying(5) |
 destination | character varying(5) |
Indexes: zips_300_origin_idx btree (origin)


 


Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now --0-286406550-1042492852=:59259--