DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=AdPY8qVYCcFYfGSWFOcoWTGa9foL5gP082NHPmS60dDNSjsMmJATwvgZJWRO8+M1NL0inFKGoyh3b+DykbBH8HteEZGp2F6/+2lhOhmjGlpvF+28eQ5t4nFEi9X3fOtGzQNKjgMQBZG75Ig03rSVHKDw0ZHz5dnCM8UKF759dVI=;
References: <7D0DD8B1-D23C-4BBC-9AB2-20D81F0B4BB8@gmail.com>
 <CAGrpgQ9=Oz5ZPgmP5JGfX_HHeHe1=z6m-JCMhhE=JautW7Xxtw@mail.gmail.com>
 <9A75BF4B-0458-43E3-803B-E25BB1984A39@gmail.com>
 <1372166429.65996.YahooMailNeo@web162901.mail.bf1.yahoo.com>
 <D05BECA9-0015-4B27-B974-B825D35DE1D9@gmail.com>
Message-ID: <1372198395.32951.YahooMailNeo@web162904.mail.bf1.yahoo.com>
Date: Tue, 25 Jun 2013 15:13:15 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: incorrect row estimates for primary key join
To: Ben <midfield@gmail.com>
Cc: bricklen <bricklen@gmail.com>,
 "pgsql-performance@postgresql.org list" <pgsql-performance@postgresql.org>
In-Reply-To: <D05BECA9-0015-4B27-B974-B825D35DE1D9@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Ben <midfield@gmail.com> wrote:

> it seems to me that an equality join between two relations (call them A a=
nd B)
> using columns in relation B with a unique constraint should yield row est=
imates
> which are at most equal to the row estimates for relation A.=A0 my questi=
ons are
>
> 1 - is this correct?
>
> 2 - does the postgresql planner implement this when generating row estima=
tes?

That seems intuitive, but some of the estimates need to be made
before all such information is available.=A0 Maybe we can do
something about that some day....

> while i do have a performance > issue (i'd like for it to select
> the index scan) which might be solved by better configuration,
> that at the moment is a secondary question -- right now i'm
> interested in why the row estimates are off.

Maybe someone else will jump in here with more details than I can
provide (at least without hours digging in the source code).

> On Jun 25, 2013, at 6:20 AM, Kevin Grittner wrote:
>> Ben <midfield@gmail.com> wrote:
>>
>>> PostgreSQL 9.1.1 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
>>> 4.6.2, 64-bit
>>
>> Consider applying the latest bug fixes for 9.1 -- which would leave
>> you showing 9.1.9.
>
> i will bring it up with our ops people.=A0 do you have any particular fix=
es in
> mind, or is this a (very sensible) blanket suggestion?

I do recommend staying up-to-date in general (subject to roll-out
procedures),=A0 We try very hard not to change any behavior that
isn't a clear bug from one minor release to the next, precisely so
that people can apply these critical bug fixes with confidence that
things won't break.=A0 There is a fix for a pretty significant
security vulnerability you are currently missing, which would be my
top concern; but it wouldn't be surprising if there were a planner
bug in 9.1.1 which is fixed in 9.1.9.

>> Do you get a different plan if you set cpu_tuple_cost =3D 0.03?=A0 How
>> about 0.05?=A0 You can set this just for a single connection and run
>> explain on the query to do a quick check.
>
> setting cpu_tuple_cost to 0.03 or 0.05 has no effect on the choice of pla=
n or
> the row estimates for the un-limited query or the limited query.

That wouldn't affect row estimates, but it would tend to encourage
index usage, because it would increase the estimated cost of
reading each row.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance