PG Bug reporting form <noreply@postgresql.org> writes:
> I was able to create a script which work on smaller data set, uses
> simplified version of the query and causes similar problem. We have also
> found that disabling seqscan allows PG to find better plan, even in terms of
> estimated cost.
I don't see any bug here, or at least nothing that we can fix in the
short run. The problem is pretty obviously that this rowcount estimate
is awful:
> -> Nested Loop (cost=0.17..736160.06 rows=1 width=16) (actual
> time=0.018..224.403 rows=100000 loops=1)
and the reason that it is awful is that the planner is not aware of
the 100% correlation between the contents of the tables, specifically
the fact that the v1 and v2 join conditions add precisely zero
selectivity beyond the id join condition.
At some point we might be able to deal with that using "extended
statistics" created on the redundant column sets, but I believe that
right now we don't apply extended stats to join conditions. Fixing
that is a future feature, not a bug fix. Right now the only advice
I can give you is to try to avoid such strongly correlated join
conditions.
BTW, the fact that it isn't noticing the marginally-cheaper-estimated-
cost plan isn't a bug either. We intentionally treat plans of essentially
equal cost as redundant and keep only one, in order to reduce planning
time. I've not traced through this example in detail, but I'm sure that
some pruning step of that kind eliminated consideration of the slightly
cheaper plan. The fact that in reality it's a *lot* cheaper escapes
the planner because of the poor selectivity estimates.
regards, tom lane