Re: query speed joining tables - Mailing list pgsql-sql

<p>my mistakes, zips_max should be zips_300. <p> <b><i>Tomasz Myrta <jasiek@klaster.net></i></b> wrote:
<blockquotestyle="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Christopher Smith wrote:<br
/><br/>> I have 4 tables that I need to query... 3 of the tables are links by the<br />> field userid.<br
/>><br/>> below are table sql fragments. as well as the query. The subquery<br />> causes very high<br
/>><br/>> CPU usages. It typically returns ~3000 matches. Is there another way to<br />> rewrite this?<br
/>><br/>> SELECT user_login.userid FROM user_login,user_details_p,user_match_details<br />> WHERE
user_login.userid= user_details_p.userid AND<br />> user_details_p.userid = user_match_details.userid AND<br />>
user_details_p.gender='W' AND<br />> user_details_p.seekgender ='M' AND<br />> user_details_p.age >=18 AND<br
/>>user_details_p.age <=50 AND<br />> user_match_details.min_age <= 30 AND<br />>
user_match_details.max_age>= 30 AND<br />> user_details_p.ethnictype = 'Caucasian (White)' AND<br />>
strpos(user_match_details.ethnicity,'Asian') !=0 AND<br />> user_details_p.zipcode in (select zips_max.destination
fromzips_max<br />> where zips_max.origin='90210' )<br />> order by user_login.last_login desc;<br /><br /><br
/>explicitjoins show better idea of your query and helps postgres <br />choosing indexing.<br /><br />select userid<br
/>from<br/>user_login<br />join user_details using (userid)<br />join user_match_details using (userid)<br />where<br
/>user_details_p.gender='W' AND<br />user_details_p.seekgender ='M' AND<br />user_details_p.age between 18 and 50
and<br/>user_match_details.min_age <= 30 AND<br />user_match_details.max_age >= 30 AND<br
/>user_details_p.ethnictype= 'Caucasian (White)' AND<br />strpos(user_match_details.ethnicity,'Asian') !=0 AND<br
/>user_details_p.zipcodein (select zips_max.destination from zips_max<br />where zips_max.origin='90210' )<br />order
byuser_login.last_login desc;<br /><br /><br />How can I help you with subquery if you didn't write even zips_max <br
/>definition?!?<br/><b r="R">If origin is unique value in that table, you can change subquery into <br />join on "from"
list.<br/><br />> Table "public.user_login"<br />> Column | Type | Modifiers<br />>
------------+--------------------------+---------------<br/>> userid | character varying(30) | not null<br />>
password| character varying(30) | not null<br />> email | character varying(50) | not null<br />> last_login |
timestampwith time zone | not null<br />> Indexes: user_login_pkey primary key btree (userid),<br /><br />Do you
reallyneed userid as varchar?<br />indexing on int4 or int8 would be much faster than varchar<br /><br />Why do you
have3 tables? It looks like only one table would be enough. <br />Remember, that null values don't take too much
space.<br/><br />><br />><br />><br />><br />> Table "public.user_details_p"<br />> Column | Type |
Modifiers<br/>> -----------------+--------------------------+---------------<br />> userid | character
varying(30)| not null<br />> gender | character varying(1) |<br />> age | integer |<br />> height | character
varying(10)|<br />> ethnicty pe | character varying(30) |<br />> education | character varying(30) |<br />>
createdate| timestamp with time zone | default now()<br />> zipcode | character varying(5) |<br />> birthdate |
date| default now()<br />> zodiac | character varying(40) |<br />> seekgender | character varying(2) |<br />>
Indexes:user_details_p_pkey primary key btree (userid),<br />> user_details_p_age_idx btree (age),<br />> &nb
sp;user_details_p_ethnic_idx btree (ethnictype),<br />> user_details_p_gender_idx btree (gender),<br /><br />><br
/>>user_details_p_last_login_idx btree (last_login),<br />> user_details_p_seekgender_idx btree (seekgender),<br
/>>user_details_p_state_idx btree (state)<br /><br />There is too many indexes -<br />index on gender (2 possible
values)is useless,<br />index on ethnic (how many values - I think not too many?) is possibly <br />useless<br /><br
/>Consider creating single index on several fields for queries like this:<br />select<br />...<br />where<br
/>user_details_p.gender='W' AND<br />user_details_p.seekgender ='M' AND<br />user_details_p.age between 18 and 50
and<br/><br />index on (age,seekgender,gender)<br /><br />><br />> Foreign Key constraints: $1 FOREIGN KEY
(userid)REFERENCES<br />> user_login(userid) ON UPDATE NO ACTI<br />> ON ON DELETE CASCADE<br />><br />><br
/>>Table "public.user_match_details"<br />> Column | Type | Modifiers<br />>
------------------+------------------------+-----------<br/>> userid | character varying(30) | not null<br />>
soughtmate| character varying(200) |<br />> ethnicity | character varying(200) |<br />> marital_status |
charactervarying(200) |<br />> min_age | integer |<br />> max_age | integer &nbs p; |<br />> city |
charactervarying(50) |<br />> state | character varying(2) |<br />> zipcode | integer |<br />> match_distance
|integer |<br />> Indexes: user_match_ details_pkey primary key btree (userid)<br />> Foreign Key constraints: $1
FOREIGNKEY (userid) REFERENCES<br />> user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE<br />><br />>
Table"public.zips_300"<br />> Column | Type | Modifiers<br />>
-------------+----------------------+-----------<br/>> origin | character varying(5) |<br />> destination |
charactervarying(5) |<br />> Indexes: zips_300_origin_idx btree (origin)<br /><br /><br />If you need more specific
answer,you have to add more information - how <br />many records do you have in your tables and how many possible
valuesdo <br />you use for example for zipcodes, ethnicity etc.<br /><br />Regards,<br />Tomasz Myrta<br
/></b></blockquote><p><br/><hr size="1" />Do you Yahoo!?<br /><a
href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Yahoo!Mail Plus</a> - Powerful. Affordable. <a
href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Signup now</a>