Re: Optimization issue of branching UNION ALL

Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> writes:
> Complaint is about auto-generated query with 1E4 simple union all's (see
> t.sh to generate a demo script). The reason: in REL_11_STABLE it is
> planned and executed in a second, but REL_12_STABLE and beyond makes
> matters worse: planning of such a query needs tons of gigabytes of RAM.

v11 (and prior versions) sucks just as badly. In this example it
accidentally escapes trouble because it doesn't know how to pull up
a subquery with empty FROM. But if you make the query look like

SELECT 1,1 FROM dual
UNION ALL
SELECT 2,2 FROM dual
UNION ALL
SELECT 3,3 FROM dual
...

then v11 chokes as well. (Seems like we've overlooked the need
for check_stack_depth() and CHECK_FOR_INTERRUPTS() here ...)

> Superficial study revealed possibly unnecessary operations that could be
> avoided:
> 1. Walking across a query by calling substitute_phv_relids() even if
> lastPHId shows that no one phv is presented.

Yeah, we could do that, and it'd help some.

> 2. Iterative passes along the append_rel_list for replacing vars in the
> translated_vars field. I can't grasp real necessity of passing all the
> append_rel_list during flattening of an union all leaf subquery. No one
> can reference this leaf, isn't it?

After thinking about that for awhile, I believe we can go further:
the containing_appendrel is actually the *only* part of the upper
query that needs to be adjusted. So we could do something like
the attached.

This passes check-world, but I don't have quite enough confidence
in it to just commit it.

regards, tom lane