Re: Using indices for UNION.

Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> writes:
> [ union_uses_idx_v2_20131113.patch ]

I'm aware that you said you were going to refactor this, but I took a
quick look through it anyway. I don't think mere refactoring is going
to make me happy with it :-(.

The basic problem here, as well as with some of the hackery that's
been proposed recently in planner.c, is that we can't really get much
further with improving this layer of the planner until we bite the
bullet and convert this layer to work with Paths not finished Plans.
Look at what you're proposing here: do a complete planning cycle on
the subquery (underneath which both ordered and unordered Paths will
be considered, and one or the other will get thrown away). Then do
*another* complete planning cycle with ordered output forced. Then
compare costs of the results. This is hugely inefficient, and it
produces far-from-ideal results too, because you're forced to make a
decision right there on which subquery plan to use; you can't make the
globally optimal choice after considering costs of all the subqueries.
What we need to do is fix things so we can get multiple Paths out of
the subquery planning step, some ordered and some not. Then we could
construct Paths for both the brute force and merge-append styles of
computing the UNION result, and finally choose the cheapest Path at the
top level.

The same goes for hacking around in grouping_planner. That function
is well past the point of collapsing of its own weight; we need
to invest some effort in refactoring before we can afford to add
much more complexity there. And I think the logical way to refactor
is to rewrite the code in a Path-generation-and-comparison style.
(Actually, grouping_planner would need to be fixed first, since
that's what would have to produce the Paths we're hoping to compare
in prepunion.)

I had hoped to make some progress on that rewrite this past summer,
but ended up with no time to work on it :-(. There's going to be
a lot of boring infrastructure work before we see much in the way
of user-visible improvement, I'm afraid, so it's kind of hard to
make time for it.

regards, tom lane