Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 84E189FB48E for ; Wed, 25 Apr 2007 21:57:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 32823-05 for ; Wed, 25 Apr 2007 21:57:00 -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.244]) by postgresql.org (Postfix) with ESMTP id AB3749FB471 for ; Wed, 25 Apr 2007 21:57:05 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so59346ana for ; Wed, 25 Apr 2007 17:57:04 -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=WvjeBrjAigNpH0anvk0JwLK2KFFin4gR4tT1kQV68ZYB/Wk7VQxLaAT6XyW+RUHZhxcVXPsEf4/fP2pJsRs+9KvOxSLJqxRad54uHx2oPV6muyV8TGQjeI9oahpMnpLyqLf+h5qJvYa59BVIHaAMmfz+nGzqUxL3vKI1YacaG+c= 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=mzyCbPFaV2aasarAFyzf0ajy9etgTc1YtYjzAVl1HWiMQ9sxRS6dob7bThXcgKqr7QoLN6oqitUKiw1cp6qqp1Xd4NfYaPYQ4zwfRD3Bd0m5/bWR/FnV9nKT+Gud9PW8IkSfpJgFEblDoxtdiSPlDLKgWGIVD9CXwJ1bHSTxStQ= Received: by 10.100.173.19 with SMTP id v19mr706382ane.1177549024333; Wed, 25 Apr 2007 17:57:04 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Wed, 25 Apr 2007 17:57:04 -0700 (PDT) Message-ID: Date: Wed, 25 Apr 2007 19:57:04 -0500 From: "Aaron Bono" To: "Tom Lane" Subject: Re: Query Join Performance Cc: "Richard Huxton" , "PostgreSQL SQL List" In-Reply-To: <6573.1177539802@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_239403_30726869.1177549024229" References: <462F9714.8090802@archonet.com> <6573.1177539802@sss.pgh.pa.us> X-Google-Sender-Auth: ee4f4f34ea19d1e6 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/253 X-Sequence-Number: 28253 ------=_Part_239403_30726869.1177549024229 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/25/07, Tom Lane wrote: > > "Aaron Bono" writes: > > 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. > > It shouldn't surprise you that joining a 900k row table to an 800k row > table > isn't cheap. > > It would certainly be better if the thing delayed the join to nearby_zip > until after it had done the restrictive join. Your problem is it > doesn't realize that that join condition is restrictive: > > > 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)) > > which is hardly surprising since the condition is phrased in a way that > isn't amenable to statistical analysis. You might want to look into > using PostGIS for this sort of thing --- it provides operators that are > better suited to the problem domain, and also allow some modicum of > intelligence in the rowcount estimates. > > Another bad misestimation is here: > > > -> 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) > > The error of 4000x here contributes directly to the error in the > top-level row estimate; but this one is a simple scalar condition and I'd > expect our stats code to be able to deal with it. Are the stats on zip > up-to-date? Maybe you need to increase the stats target for it. > > regards, tom lane > Looks like a vacuum analyze did the trick. Performance is beautiful now. I should have tried that earlier. I thought I had the auto vacuum turned on (PostgreSQL 8.1) but I guess it doesn't do analyze? Anyway, I will schedule a vacuum analyze nightly - it is low usage and won't cause any problems. Thanks! -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_239403_30726869.1177549024229 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/25/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Aaron Bono" <postgresql@aranya.com> writes:
> 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.

It shouldn't surprise you that joining a 900k row table to an 800k row table
isn't cheap.

It would certainly be better if the thing delayed the join to nearby_zip
until after it had done the restrictive join.  Your problem is it
doesn't realize that that join condition is restrictive:

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

which is hardly surprising since the condition is phrased in a way that
isn't amenable to statistical analysis.  You might want to look into
using PostGIS for this sort of thing --- it provides operators that are
better suited to the problem domain, and also allow some modicum of
intelligence in the rowcount estimates.

Another bad misestimation is here:

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

The error of 4000x here contributes directly to the error in the
top-level row estimate; but this one is a simple scalar condition and I'd
expect our stats code to be able to deal with it.  Are the stats on zip
up-to-date?  Maybe you need to increase the stats target for it.

                        regards, tom lane

Looks like a vacuum analyze did the trick.  Performance is beautiful now.  I should have tried that earlier.

I thought I had the auto vacuum turned on (PostgreSQL 8.1) but I guess it doesn't do analyze?

Anyway, I will schedule a vacuum analyze nightly - it is low usage and won't cause any problems.

Thanks!

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