Hello,
Planner seems to make a weird choice of join algorithm -- O(M*N)
nestloop -- under certain circumstances.
I'm not exactly sure what is the criteria but I have a self-contained
example, albeit a large one.
So if you unpack the pb-dump.sql.bz2 attached and run the pb-test.sql
you'll have some plan explained.
Despite an index present on "qux" it's accessed using a plain seq-seq
nestloop, see pb-plan.txt
Why? Cardinalities look like predicted reasonably well, and with those
predictions hash join or index scan would
be obviously faster: the planner thinks we are joining 149 and 175728 rows.
The distribution for "qux"."foo_id" is not too skew, the average number
of rows per "foo_id" in "qux" is about 9.
Slight data or query variations make it use the index.
With "set enable_nestloop to off; set enable_mergejoin to off;" the plan
generated is better. It has smaller cost of the final join, though the
costs for the outer relation increase, probably due to the "never
executed" path.
Playing with json/from_collapse_limit does not make any difference.
I can observe this on both master and v 10.11 .
I haven't investigated it any further yet, so for now just asking
whether it's a known behavior?
If not, I'll try to find out what's going on.
Best, Alex