From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Kratz <chris.kratz@vistashare.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Incorrect estimates on columns
Date: Wed, 17 Oct 2007 14:49:34 -0400
Message-ID: <9262.1192646974@sss.pgh.pa.us> (raw)
In-Reply-To: <200710171334.24526.chris.kratz@vistashare.com>
References: <200710171334.24526.chris.kratz@vistashare.com>
Chris Kratz <chris.kratz@vistashare.com> writes:
> I'm struggling to get postgres to run a particular query quickly.
The key problem seems to be the join size misestimate here:
> -> Hash Join (cost=45.92..1251.07 rows=21 width=8) (actual time=0.948..10.439 rows=1199 loops=1)
> Hash Cond: (par.activity = a.activityid)
> -> Bitmap Heap Scan on rpt_agencyquestioncache_171_0 par (cost=21.92..1222.19 rows=1247 width=8) (actual time=0.415..3.081 rows=1199 loops=1)
> -> Hash (cost=19.21..19.21 rows=383 width=4) (actual time=0.513..0.513 rows=383 loops=1)
Evidently it's not realizing that every row of par will have a join
partner, but why not? I suppose a.activityid is unique, and in most
cases that I've seen the code seems to get that case right.
Would you show us the pg_stats rows for par.activity and a.activityid?
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chris.kratz@vistashare.com
Subject: Re: Incorrect estimates on columns
In-Reply-To: <9262.1192646974@sss.pgh.pa.us>
* 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