From: Christopher Smith <christopherl_smith@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: query speed joining tables
Date: Mon, 13 Jan 2003 13:20:52 -0800 (PST)
Message-ID: <20030113212052.60493.qmail@web14107.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: christopherl_smith@yahoo.com
Subject: Re: query speed joining tables
In-Reply-To: <20030113212052.60493.qmail@web14107.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox