From: Tomasz Myrta <jasiek@klaster.net>
To: Christopher Smith <christopherl_smith@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
Date: Tue, 14 Jan 2003 08:12:45 +0100
Message-ID: <3E23B86D.8020600@klaster.net> (raw)
In-Reply-To: <20030113220300.69330.qmail@web14107.mail.yahoo.com>
References: <20030113220300.69330.qmail@web14107.mail.yahoo.com>
Christopher Smith wrote:
>my mistakes, zips_max should be zips_300.
>and
>in my zip code table there are 120 million rows, example of the
records >are
>
>origin destination
>===================
>
>90210 90222
>90210 90234
>90210 96753
1.try to create index on both fields on zips_300 - origin and destination
zips_300_ind(origin,destination)
2.if you have only unique pairs in zips_300, this query should noticable
speed up you example:
select userid
from
user_login UL
join user_details_p UD using (userid)
join user_match_details UM using (userid)
join zips_300 Z on (Z.destination=UM.zipcode and Z.origin='90210')
where
UD.gender ='W' AND
UD.seekgender ='M' AND
UD.age between 18 and 50 and
UMD.min_age <= 30 AND
UMD.max_age >= 30 AND
UD.ethnictype = 'Caucasian (White)' AND
strpos(UMD.ethnicity,'Asian') !=0
order by user_login.last_login desc;
Next step to speed up your query is answering such question:
- How many values do I get if I ask one question.
Example:
gender='W' - 50% rows
seekgender='M' - 50% rows
ethnictype='Caucasian (White)' - 5%
Start indexing your tables on smallest values - in this situation -
ethnictype. Consider using multi-column indexes.
Regards,
Tomasz Myrta
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: jasiek@klaster.net, christopherl_smith@yahoo.com
Subject: Re: query speed joining tables
In-Reply-To: <3E23B86D.8020600@klaster.net>
* 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