Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2F85A9FB9F7 for ; Wed, 25 Apr 2007 17:40:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 00928-01 for ; Wed, 25 Apr 2007 17:39:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id CA5D69FA100 for ; Wed, 25 Apr 2007 17:39:56 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so229325ana for ; Wed, 25 Apr 2007 13:39:55 -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:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=LtXfaqs3DFxLuxBeT5Y8wXg5i/CX9byE2PPQn1PNyDpNthLQB5QyFfla8CXRiZiuNIHb7LGnPyaL3rumTVzPi9zzlvDbMj9Sx+35SUy50wD06DRatDnwb8DDnwzP6uyfheSChaqO3oPyoLCzJC58vzB55bumIHxRAYnjVW1lI44= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=A37RaD4A62yAS463IBxoD9g8kJIlvx88dAlSrKAHPDKeLzMRX8jdgr5oOryFxqU/fwaY5yzo9uX/SG0FajXzkke+OcanVebUlhYuxbP+84rvqtx83pCY5K0nsKjyBHk6F0PcFD204R+oIsrdmGNLrQD89tZMojWZzSIGRpmkzeU= Received: by 10.100.205.9 with SMTP id c9mr567998ang.1177533595709; Wed, 25 Apr 2007 13:39:55 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Wed, 25 Apr 2007 13:39:55 -0700 (PDT) Message-ID: Date: Wed, 25 Apr 2007 15:39:55 -0500 From: "Aaron Bono" To: "Richard Huxton" Subject: Re: Query Join Performance Cc: "PostgreSQL SQL List" In-Reply-To: <462F9714.8090802@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_236375_9446224.1177533595465" References: <462F9714.8090802@archonet.com> X-Google-Sender-Auth: a6bda73619dceaa2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/251 X-Sequence-Number: 28251 ------=_Part_236375_9446224.1177533595465 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/25/07, Richard Huxton wrote: > > Aaron Bono wrote: > > Performance tanks with this query - it takes over 120 seconds (that is > > where > > I set the timeout). > > > 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. > > So what's different between the systems. Obvious things to look at: > 1. EXPLAIN ANALYSE outputs to show the plans (these presumably are > different, but in what details, and why?) > 2. Configuration (particularly memory/cost settings). > 3. Hardware. > 4. Locale/encoding - these can affect index usage and sorting. > > -- > Richard Huxton > Archonet Ltd > Now I am having the same problem on the Linux box so I doubt it is the platform. The biggest problem I notice is when I add a join from a child table (zip_city) to a parent table (zip). I have filtered the child table down to about 650 records but when I add the join to the parent which has over 800,000 records, performance tanks. I was able to benchmark two queries last night on my Windows machine: -- This runs in just over 2 seconds select nearby_zip_city.zip_id, gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, nearby_zip_city.longitude::numeric, nearby_zip_city.latitude::numeric) AS distance from zip_city inner join zip on ( zip.zip_id = zip_city.zip_id ) inner join zip_city as nearby_zip_city on ( abs(zip_city.longitude - nearby_zip_city.longitude) <= (50.0 / 60.0) AND abs(zip_city.latitude - nearby_zip_city.latitude) <= (50.0 / 60.0) ) where zip.zip_cd = '66105' -- This takes over 48 seconds and I just added a join from the zip_city child to the zip parent table select nearby_zip.zip_cd, gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, nearby_zip_city.longitude::numeric, nearby_zip_city.latitude::numeric) AS distance from zip_city inner join zip on ( zip.zip_id = zip_city.zip_id ) inner join zip_city as nearby_zip_city on ( abs(zip_city.longitude - nearby_zip_city.longitude) <= (50.0 / 60.0) AND abs(zip_city.latitude - nearby_zip_city.latitude) <= (50.0 / 60.0) ) -->>> The next 3 lines are the main difference <<<-- inner join zip as nearby_zip on ( nearby_zip_city.zip_id = nearby_zip.zip_id ) -->>> End of difference <<<-- where zip.zip_cd = '66105' -- Explain plan for faster/first query: Nested Loop (cost=45779.82..147990502.45 rows=451678770 width=40) (actual time=5404.943..20151.684 rows=653 loops=1) Join Filter: ((abs(("inner".longitude - "outer".longitude)) <= 0.833333333333333::double precision) AND (abs(("inner".latitude - "outer".latitude)) <= 0.833333333333333::double precision)) -> Seq Scan on zip_city nearby_zip_city (cost=0.00..25514.19 rows=901719 width=24) (actual time=0.053..2311.547 rows=901719 loops=1) -> Materialize (cost=45779.82..45824.90 rows=4508 width=16) (actual time=0.003..0.009 rows=1 loops=901719) -> Hash Join (cost=8944.55..45775.31 rows=4508 width=16) (actual time=444.657..4490.901 rows=1 loops=1) Hash Cond: ("outer".zip_id = "inner".zip_id) -> Seq Scan on zip_city (cost=0.00..25514.19 rows=901719 width=24) (actual time=0.004..2334.548 rows=901719 loops=1) -> Hash (cost=8933.90..8933.90 rows=4261 width=8) (actual time=0.296..0.296 rows=1 loops=1) -> Bitmap Heap Scan on zip (cost=32.91..8933.90rows=4261 width=8) (actual time= 0.272..0.275 rows=1 loops=1) Recheck Cond: ((zip_cd)::text = '66105'::text) -> Bitmap Index Scan on zip_zip_cd_key (cost= 0.00..32.91 rows=4261 width=0) (actual time=0.250..0.250 rows=1 loops=1) Index Cond: ((zip_cd)::text = '66105'::text) -- Explain plan for shower/second query: Nested Loop (cost=75372.31..148056286.32 rows=451678770 width=43) (actual time=62688.188..69916.943 rows=653 loops=1) Join Filter: ((abs(("inner".longitude - "outer".longitude)) <= 0.833333333333333::double precision) AND (abs(("inner".latitude - "outer".latitude)) <= 0.833333333333333::double precision)) -> Hash Join (cost=29592.49..91298.06 rows=901719 width=27) (actual time=17905.224..52279.151 rows=901719 loops=1) Hash Cond: ("outer".zip_id = "inner".zip_id) -> Seq Scan on zip_city nearby_zip_city (cost=0.00..25514.19rows=901719 width=24) (actual time= 0.044..2888.993 rows=901719 loops=1) -> Hash (cost=21634.79..21634.79 rows=852279 width=19) (actual time=13925.502..13925.502 rows=852279 loops=1) -> Seq Scan on zip nearby_zip (cost=0.00..21634.79rows=852279 width=19) (actual time= 0.042..2535.742 rows=852279 loops=1) -> Materialize (cost=45779.82..45824.90 rows=4508 width=16) (actual time=0.002..0.009 rows=1 loops=901719) -> Hash Join (cost=8944.55..45775.31 rows=4508 width=16) (actual time=421.374..4453.224 rows=1 loops=1) Hash Cond: ("outer".zip_id = "inner".zip_id) -> Seq Scan on zip_city (cost=0.00..25514.19 rows=901719 width=24) (actual time=0.028..2333.941 rows=901719 loops=1) -> Hash (cost=8933.90..8933.90 rows=4261 width=8) (actual time=0.604..0.604 rows=1 loops=1) -> Bitmap Heap Scan on zip (cost=32.91..8933.90rows=4261 width=8) (actual time= 0.588..0.591 rows=1 loops=1) Recheck Cond: ((zip_cd)::text = '66105'::text) -> Bitmap Index Scan on zip_zip_cd_key (cost= 0.00..32.91 rows=4261 width=0) (actual time=0.559..0.559 rows=1 loops=1) Index Cond: ((zip_cd)::text = '66105'::text) It is the join that is killing the query but I am at a loss of the best approach to fix it. I have some work arounds in mind by flattening out the tables but I would rather not have to do that. Thanks! Aaron -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_236375_9446224.1177533595465 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/25/07, Richard Huxton <dev@archonet.com> wrote:
Aaron Bono wrote:
> Performance tanks with this query - it takes over 120 seconds (that is
> where
> I set the timeout).

> 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.

So what's different between the systems. Obvious things to look at:
1. EXPLAIN ANALYSE outputs to show the plans (these presumably are
different, but in what details, and why?)
2. Configuration (particularly memory/cost settings).
3. Hardware.
4. Locale/encoding - these can affect index usage and sorting.

--
   Richard Huxton
   Archonet Ltd

Now I am having the same problem on the Linux box so I doubt it is the platform.

The biggest problem I notice is when I add a join from a child table (zip_city) to a parent table (zip).  I have filtered the child table down to about 650 records but when I add the join to the parent which has over 800,000 records, performance tanks.  I was able to benchmark two queries last night on my Windows machine:

-- This runs in just over 2 seconds

    select
        nearby_zip_city.zip_id,
        gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, nearby_zip_city.longitude::numeric, nearby_zip_city.latitude::numeric) AS distance
    from zip_city
    inner join zip on (
        zip.zip_id = zip_city.zip_id
    )
    inner join zip_city as nearby_zip_city on (
        abs(zip_city.longitude - nearby_zip_city.longitude) <= (50.0 / 60.0)
        AND abs(zip_city.latitude - nearby_zip_city.latitude) <= (50.0 / 60.0)
    )
    where zip.zip_cd = '66105'

-- This takes over 48 seconds and I just added a join from the zip_city child to the zip parent table

    select
        nearby_zip.zip_cd,
        gps_distance(zip_city.longitude::numeric, zip_city.latitude::numeric, nearby_zip_city.longitude::numeric, nearby_zip_city.latitude::numeric) AS distance
    from zip_city
    inner join zip on (
        zip.zip_id = zip_city.zip_id
    )
    inner join zip_city as nearby_zip_city on (
        abs(zip_city.longitude - nearby_zip_city.longitude) <= (50.0 / 60.0)
        AND abs(zip_city.latitude - nearby_zip_city.latitude) <= ( 50.0 / 60.0)
    )
    -->>> The next 3 lines are the main difference <<<--
    inner join zip as nearby_zip on (
        nearby_zip_city.zip_id = nearby_zip.zip_id
    )
    -->>> End of difference <<<--
    where zip.zip_cd = '66105'

-- Explain plan for faster/first query:
Nested Loop  (cost=45779.82..147990502.45 rows=451678770 width=40) (actual time=5404.943..20151.684 rows=653 loops=1)
  Join Filter: ((abs(("inner".longitude - "outer".longitude)) <= 0.833333333333333::double precision) AND (abs(("inner".latitude - "outer".latitude)) <= 0.833333333333333::double precision))
  ->  Seq Scan on zip_city nearby_zip_city  (cost=0.00..25514.19 rows=901719 width=24) (actual time= 0.053..2311.547 rows=901719 loops=1)
  ->  Materialize  (cost=45779.82..45824.90 rows=4508 width=16) (actual time=0.003..0.009 rows=1 loops=901719)
        ->  Hash Join  (cost=8944.55..45775.31 rows=4508 width=16) (actual time= 444.657..4490.901 rows=1 loops=1)
              Hash Cond: ("outer".zip_id = "inner".zip_id)
              ->  Seq Scan on zip_city  (cost=0.00..25514.19 rows=901719 width=24) (actual time=0.004..2334.548 rows=901719 loops=1)
              ->  Hash  (cost=8933.90..8933.90 rows=4261 width=8) (actual time=0.296..0.296 rows=1 loops=1)
                    ->  Bitmap Heap Scan on zip  (cost=32.91..8933.90 rows=4261 width=8) (actual time= 0.272..0.275 rows=1 loops=1)
                          Recheck Cond: ((zip_cd)::text = '66105'::text)
                          ->  Bitmap Index Scan on zip_zip_cd_key  (cost=0.00..32.91 rows=4261 width=0) (actual time= 0.250..0.250 rows=1 loops=1)
                                Index Cond: ((zip_cd)::text = '66105'::text)

-- Explain plan for shower/second query:
Nested Loop  (cost=75372.31..148056286.32 rows=451678770 width=43) (actual time= 62688.188..69916.943 rows=653 loops=1)
  Join Filter: ((abs(("inner".longitude - "outer".longitude)) <= 0.833333333333333::double precision) AND (abs(("inner".latitude - "outer".latitude)) <= 0.833333333333333::double precision))
  ->  Hash Join  (cost=29592.49..91298.06 rows=901719 width=27) (actual time=17905.224..52279.151 rows=901719 loops=1)
        Hash Cond: ("outer".zip_id = "inner".zip_id)
        ->  Seq Scan on zip_city nearby_zip_city  (cost=0.00..25514.19 rows=901719 width=24) (actual time=0.044..2888.993 rows=901719 loops=1)
        ->  Hash  (cost=21634.79..21634.79 rows=852279 width=19) (actual time= 13925.502..13925.502 rows=852279 loops=1)
              ->  Seq Scan on zip nearby_zip  (cost=0.00..21634.79 rows=852279 width=19) (actual time=0.042..2535.742 rows=852279 loops=1)
  ->  Materialize  (cost=45779.82..45824.90 rows=4508 width=16) (actual time=0.002..0.009 rows=1 loops=901719)
        ->  Hash Join  (cost=8944.55..45775.31 rows=4508 width=16) (actual time=421.374..4453.224 rows=1 loops=1)
              Hash Cond: ("outer".zip_id = "inner".zip_id)
              ->  Seq Scan on zip_city  (cost=0.00..25514.19 rows=901719 width=24) (actual time=0.028..2333.941 rows=901719 loops=1)
              ->  Hash  (cost=8933.90..8933.90 rows=4261 width=8) (actual time= 0.604..0.604 rows=1 loops=1)
                    ->  Bitmap Heap Scan on zip  (cost=32.91..8933.90 rows=4261 width=8) (actual time=0.588..0.591 rows=1 loops=1)
                          Recheck Cond: ((zip_cd)::text = '66105'::text)
                          ->  Bitmap Index Scan on zip_zip_cd_key  (cost=0.00..32.91 rows=4261 width=0) (actual time=0.559..0.559 rows=1 loops=1)
                                Index Cond: ((zip_cd)::text = '66105'::text)

It is the join that is killing the query but I am at a loss of the best approach to fix it.  I have some work arounds in mind by flattening out the tables but I would rather not have to do that.

Thanks!
Aaron

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