From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <kgrittn@ymail.com>
Cc: Ben <midfield@gmail.com>
Cc: bricklen <bricklen@gmail.com>
Cc: pgsql-performance@postgresql.org list <pgsql-performance@postgresql.org>
Subject: Re: incorrect row estimates for primary key join
Date: Tue, 25 Jun 2013 19:36:17 -0400
Message-ID: <19479.1372203377@sss.pgh.pa.us> (raw)
In-Reply-To: <1372198395.32951.YahooMailNeo@web162904.mail.bf1.yahoo.com>
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>
<1372198395.32951.YahooMailNeo@web162904.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Kevin Grittner <kgrittn@ymail.com> writes:
> Ben <midfield@gmail.com> wrote:
>> it seems to me that an equality join between two relations (call them A and B)
>> using columns in relation B with a unique constraint should yield row estimates
>> which are at most equal to the row estimates for relation A. my questions are
>>
>> 1 - is this correct?
>>
>> 2 - does the postgresql planner implement this when generating row estimates?
> That seems intuitive, but some of the estimates need to be made
> before all such information is available. Maybe we can do
> something about that some day....
> Maybe someone else will jump in here with more details than I can
> provide (at least without hours digging in the source code).
It does not attempt to match up query WHERE clauses with indexes during
selectivity estimation, so the existence of a multi-column unique
constraint wouldn't help it improve the estimate.
In the case at hand, I doubt that a better result rowcount estimate
would have changed the planner's opinion of how to do the join. The OP
seems to be imagining that 2 million index probes into a large table
would be cheap, but that's hardly true. It's quite likely that the
mergejoin actually is the best way to do the query. If it isn't really
best on his hardware, I would think that indicates a need for some
tuning of the cost parameters. Another thing that might be helpful for
working with such large tables is increasing work_mem, to make hashes
and sorts run faster.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, kgrittn@ymail.com, midfield@gmail.com, bricklen@gmail.com
Subject: Re: incorrect row estimates for primary key join
In-Reply-To: <19479.1372203377@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