Re: Join Filter vs. Index Cond (performance regression 9.1->9.2+/HEAD)

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> Once you're down to an estimate of one row retrieved, adding
Tom> additional index conditions simply increases the cost (not by
Tom> much, but it increases) without delivering any visible benefit.

OK, but this is a serious problem because "estimate of one row" is a
very common estimation failure mode, and isn't always solvable in the
sense of arranging for better estimates (in the absence of hints, ugh).

Tom> I believe what probably happened in this case is that the planner
Tom> considered both forms of the indexscan path and concluded that
Tom> they were fuzzily the same cost and rowcount, yet the path using
Tom> only t2.a and t3.b clearly dominated by requiring strictly fewer
Tom> outer relations for parameters. So it threw away the path that
Tom> also had the c = t4.c comparison before it ever got to the join
Tom> stage. Even had it kept that path, the join cost estimate
Tom> wouldn't have looked any better than the one for the join it did
Tom> pick, so there would have been no certainty of picking the
Tom> "correct" plan.

Tom> The real problem in your example is thus the incorrect rowcount
Tom> estimate; with better rowcount estimates the two cases wouldn't
Tom> have appeared to have the same output rowcount.

Tom> For the toy data in your example, this can probably be blamed on
Tom> the fact that eqjoinsel_inner doesn't have any smarts for the case
Tom> of having an MCV list for only one side (though as noted in the
Tom> comments, it's not obvious what it should do instead). However,
Tom> it's not very clear what was happening in the real-world case.

In the real-world case, t1 was something like an "overrides" table for
data otherwise obtained from the other tables, i.e. special-case
exceptions for general rules. As such it is highly skew, with many
possible (a,b) values having no row at all, but others having hundreds
of matches on (a,b) (but only one at most on (a,b,c) since this was the
pkey in the real data as well as the testcase).

Accordingly, there was no way that we could identify of getting any kind
of better estimate of rowcount.

--
Andrew (irc:RhodiumToad)