From: Aaron Bono <postgresql@aranya.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>
Cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Re: Query Join Performance
Date: Wed, 25 Apr 2007 19:57:04 -0500
Message-ID: <bf05e51c0704251757o311dd849n2c6a52e1c68b933e@mail.gmail.com> (raw)
In-Reply-To: <6573.1177539802@sss.pgh.pa.us>
References: <bf05e51c0704241826u3e6652d1m26973b837e0b1397@mail.gmail.com>
<462F9714.8090802@archonet.com>
<bf05e51c0704251339u2883211eh4cdedcbec63c2be9@mail.gmail.com>
<6573.1177539802@sss.pgh.pa.us>
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.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Query Join Performance
In-Reply-To: <bf05e51c0704251757o311dd849n2c6a52e1c68b933e@mail.gmail.com>
* 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