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....
> 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. do you have any particular fixes in
> mind, or is this a (very sensible) blanket suggestion?
I do recommend staying up-to-date in general (subject to roll-out
procedures), 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. 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 = 0.03? How
>> about 0.05? 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 plan 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
