Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 06BD59FA425 for ; Tue, 24 Apr 2007 22:26:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03866-04 for ; Tue, 24 Apr 2007 22:26:17 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.232]) by postgresql.org (Postfix) with ESMTP id E46829FA434 for ; Tue, 24 Apr 2007 22:26:16 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i28so54871wxd for ; Tue, 24 Apr 2007 18:26:15 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=iwhqPyD3IUWljEL2GeFsWHtOYIPVEX74KH7tO7IY04oD/pNMl13zUTNLNWEk/encuHZyni1XDn9MKqeRpPTklnQgWa8zuxCZT+CnVX3rI+UQqZhLjIUfPD3tFRfA+SzsgAXfIlrxn8t6aotQoSyW8dROfWhK/GOFTV4N9KjHywY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth; b=rp9DoglSojRDyXIrnyLJhU6cQ06u/KsJYfOkY4p+SksxWALZ0gFkjG6vdZApHw3txZbU08RoXdYDNTmBYB7zttCfpExSLj/KwuqXPZ5r779eBOdScsLG+Xt5u6K+gZhag56fMtSh2lAoNmoli6gdvI6qZhUd0W5jDMUXIG65YOI= Received: by 10.70.131.19 with SMTP id e19mr186001wxd.1177464375610; Tue, 24 Apr 2007 18:26:15 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Tue, 24 Apr 2007 18:26:15 -0700 (PDT) Message-ID: Date: Tue, 24 Apr 2007 20:26:15 -0500 From: "Aaron Bono" To: "PostgreSQL SQL List" Subject: Query Join Performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_218690_28190242.1177464375552" X-Google-Sender-Auth: e126276b7a860b96 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/236 X-Sequence-Number: 28236 ------=_Part_218690_28190242.1177464375552 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a database with the following tables: zip { zip_id bigserial, zip_cd varchar(6) -- this is indexed } city { city_id bigserial, city_name varchar(50) } zip_city { city_id bigint (FK to city table), zip_id bigint (FK to zip table), longitude numeric, latitude numeric } We want to find the zip_cd values for all zip codes within 50 miles of a specified zip code and have this query so far (assume our given zip code is 64131): select zip_city.zip_id, gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, my_location.longitude::numeric, my_location.latitude::numeric) AS distance from zip_city inner join ( select longitude, latitude from zip inner join zip_city on ( zip.zip_id = zip_city.zip_id ) where zip_cd = '64131' ) my_location on ( -- We use this as an approximation to filter out as many records as possible before doing the gps_distance() function call -- It works well as a outer bounds to get the most obvious outside zip_city values. abs(zip_city.longitude - my_location.longitude) <= (50.0 / 60.0) AND abs(zip_city.latitude - my_location.latitude) <= (50.0 / 60.0) ) This runs in about 2 seconds on Windows and gives me 650 records - not bad. When I add just a little bit: select zip_city.zip_id, gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, my_location.longitude::numeric, my_location.latitude::numeric) AS distance from zip_city -->>>> THE ONLY DIFFERENCE IS THE NEXT 3 LINES <<<<-- inner join zip on ( zip_city.zip_id = zip.zip_id ) -->>>> End of added code <<<<-- inner join ( select longitude, latitude from zip inner join zip_city on ( zip.zip_id = zip_city.zip_id ) where zip_cd = '64131' ) my_location on ( abs(zip_city.longitude - my_location.longitude) <= (50.0 / 60.0) AND abs(zip_city.latitude - my_location.latitude) <= (50.0 / 60.0) ) Performance tanks with this query - it takes over 120 seconds (that is where I set the timeout). I read that a foreign key doesn't help performance and my guess is that PostgreSQL isn't just joining on the 650 records but rather on the over 800,000 records in the zip table (yes we have that many - we include Canada zip codes which is 90% of the data - Blame Canada! ;). Is there something I can do to boost performance? I tried putting the first query above in an inner join before joining with the zip table with the same result. BTW, on our Linux box the full query we run (which adds 3 more tables on the whole operation along with more filtering on the zip table) finishes in under 10 seconds. Problem is our development is on Windows and this is a real pain for developers to test. Any assistance is appreciated. I did look at the explain plans but I don't see anything other than what I mentioned above. I can include those if necessary. Thanks! Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_218690_28190242.1177464375552 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a database with the following tables:

zip {
    zip_id bigserial,
    zip_cd varchar(6) -- this is indexed
}

city {
    city_id bigserial,
    city_name varchar(50)
}

zip_city {
    city_id bigint (FK to city table),
    zip_id bigint (FK to zip table),
    longitude numeric,
    latitude numeric
}

We want to find the zip_cd values for all zip codes within 50 miles of a specified zip code and have this query so far (assume our given zip code is 64131):

    select
        zip_city.zip_id,
        gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, my_location.longitude::numeric, my_location.latitude::numeric) AS distance
    from zip_city
    inner join (
        select
            longitude,
            latitude
        from zip
        inner join zip_city on (
            zip.zip_id = zip_city.zip_id
        )
        where zip_cd = '64131'
    ) my_location on (
        -- We use this as an approximation to filter out as many records as possible before doing the gps_distance() function call
        -- It works well as a outer bounds to get the most obvious outside zip_city values.
        abs(zip_city.longitude - my_location.longitude) <= (50.0 / 60.0)
        AND abs(zip_city.latitude - my_location.latitude) <= (50.0 / 60.0)
    )

This runs in about 2 seconds on Windows and gives me 650 records - not bad.

When I add just a little bit:

    select
        zip_city.zip_id,
        gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, my_location.longitude::numeric, my_location.latitude::numeric) AS distance
    from zip_city
    -->>>> THE ONLY DIFFERENCE IS THE NEXT 3 LINES <<<<--
    inner join zip on (
        zip_city.zip_id = zip.zip_id
    )
    -->>>> End of added code <<<<--
    inner join (
        select
            longitude,
            latitude
        from zip
        inner join zip_city on (
            zip.zip_id = zip_city.zip_id
        )
        where zip_cd = '64131'
    ) my_location on (
        abs(zip_city.longitude - my_location.longitude) <= (50.0 / 60.0)
        AND abs(zip_city.latitude - my_location.latitude) <= (50.0 / 60.0)
    )

Performance tanks with this query - it takes over 120 seconds (that is where I set the timeout).  I read that a foreign key doesn't help performance and my guess is that PostgreSQL isn't just joining on the 650 records but rather on the over 800,000 records in the zip table (yes we have that many - we include Canada zip codes which is 90% of the data - Blame Canada! ;).

Is there something I can do to boost performance?  I tried putting the first query above in an inner join before joining with the zip table with the same result.

BTW, on our Linux box the full query we run (which adds 3 more tables on the whole operation along with more filtering on the zip table) finishes in under 10 seconds.  Problem is our development is on Windows and this is a real pain for developers to test.

Any assistance is appreciated.  I did look at the explain plans but I don't see anything other than what I mentioned above.  I can include those if necessary.

Thanks!
Aaron

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_218690_28190242.1177464375552--