Re: Clamping reulst row number of joins.

I wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>> I've certainly seen and used values() constructs in joins for a variety
>>> of reasons and I do think it'd be worthwhile for the planner to know how
>>> to pull up a VALUES construct.

> I spent a bit of time looking at this, and realized that the blocker
> is the same as the reason why we don't pull up sub-selects with empty
> rangetables ("SELECT expression(s)"). Namely, that the upper query's
> jointree can't handle a null subtree. (This is not only a matter of
> the jointree data structure, but the fact that the whole planner
> identifies relations by bitmapsets of RTE indexes, and subtrees with
> empty RTE sets couldn't be told apart.)

> We could probably fix both cases for order-of-a-hundred lines of new code
> in prepjointree. The plan I'm thinking about is to allow such vacuous
> subquery jointrees to be pulled up, but only if they are in a place in
> the upper query's jointree where it's okay to delete the subtree. This
> would basically be two cases: (1) the immediate parent is a FromExpr that
> would have at least one remaining child, or (2) the immediate parent is
> an INNER JOIN whose other child isn't also being deleted (so that we can
> convert the JoinExpr to a nonempty FromExpr, or just use the other child
> as-is if the JoinExpr has no quals).

Here's a draft patch along those lines. Unsurprisingly, it changes the
plans generated for a number of regression-test queries. In most cases
I felt it desirable to force the old plan shape to be retained (by
inserting "offset 0" or equivalent) because the test was trying to test
proper generation of a query plan of that shape. I did add a couple
cases where the optimization was allowed to go through.

The patch is a bit bigger than I'd hoped (a net of about 330 lines added
to prepjointree.c), but it's not hugely ugly, and it doesn't add any
meaningful overhead in cases where no optimization happens. Barring
objections I will commit this.

regards, tom lane