Re: Ordered Partitioned Table Scans

On Mon, 5 Nov 2018 at 10:46, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 1 November 2018 at 22:05, Antonin Houska <ah(at)cybertec(dot)at> wrote:
> > I think these conditions are too restrictive:
> >
> > /*
> > * Determine if these pathkeys match the partition order, or reverse
> > * partition order. It can't match both, so only go to the trouble of
> > * checking the reverse order when it's not in ascending partition
> > * order.
> > */
> > partition_order = pathkeys_contained_in(pathkeys,
> > partition_pathkeys);
> > partition_order_desc = !partition_order &&
> > pathkeys_contained_in(pathkeys,
> > partition_pathkeys_desc);
> >

> The problem with doing that is that if the partition keys are better
> than the pathkeys then we'll most likely fail to generate any
> partition keys at all due to lack of any existing eclass to use for
> the pathkeys. It's unsafe to use just the prefix in this case as the
> eclass may not have been found due to, for example one of the
> partition keys having a different collation than the required sort
> order of the query. In other words, we can't rely on a failure to
> create the pathkey meaning that a more strict sort order is not
> required.

I had another look at this patch and it seems okay just to add a new
flag to build_partition_pathkeys() to indicate if the pathkey List was
truncated or not. In generate_mergeappend_paths() we can then just
check that flag before checking if the partiiton pathkeys are
contained in pathkeys. It's fine if the partition keys were truncated
for the reverse of that check.

I've done this in the attached and added additional regression tests
for this case.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services