Message-ID: <3E23B86D.8020600@klaster.net>
Date: Tue, 14 Jan 2003 08:12:45 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Christopher Smith <christopherl_smith@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
References: <20030113220300.69330.qmail@web14107.mail.yahoo.com>
In-Reply-To: <20030113220300.69330.qmail@web14107.mail.yahoo.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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