Re: UNION ALL on partitioned tables won't use indices. - Mailing list pgsql-hackers

Thank you.

I'll soon come up with regress for the patch 3, 4.

=====
> > See commit dd4134ea, which added that text.  IIRC, the key point is that
> > among "real" EC members, there will never be duplicates: a Var "x.y"
> > for instance cannot appear in two different ECs, because we'd have merged
> > the two ECs into one instead.  However, this property does *not* hold for
> > child EC members.  The problem is that two completely unrelated UNION ALL
> > child subselects might have, say, constant "1" in their tlists.  This
> > should not lead to concluding that we can merge ECs that mention their
> > UNION ALL parent variables.
> Thanks for the pointer; I found things clearer after reviewing the ECs from
> the test case queries from commits dd4134ea and 57664ed2.

Is it correct that ec-members are parted into two groups one of
which is 'full-fledged' and never duplicate among different ECs
so as to be useful for forming pathkeys (parent) and another is
immatured and might be duplicate which could not form
pathkeys. Thus the new-in-patch-1 third group which is not
duplicate (because they should be always be Var "x.y" (for this
case)) but immatured (child) currently can not be adequately
classified?
My PATCH-1 which make them in the third group forcibly
classified as 'parent' instead of 'child' as before therefore
makes 'broken' ec member?

> > I've not looked at these patches yet, but if they're touching equivclass.c
> > at all, I'd guess that it's wrong.
> 
> Only PATCH-1 touched equivclass.c.

Surely.

> > My gut feeling after two minutes' thought is that the best fix is for
> > expand_inherited_rtentry to notice when the parent table is already an
> > appendrel member, and enlarge that appendrel instead of making a new one.
> > (This depends on the assumption that pull_up_simple_union_all always
> > happens first, but that seems OK.)  I'm not sure if that concept exactly
> > corresponds to either PATCH-3 or PATCH-4, but that's the way I'd think
> > about it.
> 
> PATCH-4 does approximately that.  I agree that's the right
> general direction.

Ok, I'll provide regress for the patches 3 and 4.

> An alternative to extending our ability to pull up UNION ALL subqueries,
> having perhaps broader applicability, would be to push down the
> possibly-useful sort order to subqueries we can't pull up.  We'd sometimes
> have two levels of MergeAppend, but that could still handily beat the
> explicit-sort plan.  In any case, it is indeed a separate topic.  For the sake
> of the archives, you can get such plans today by manually adding the ORDER BY
> to the relevant UNION ALL branch:

Yes, but this seems could be done in path-generation phase or
earlier (that's mere a smattering).

-- 
Kyotaro Horiguchi
NTT Open Source Software Center