Re: Ordered Partitioned Table Scans

On 31 October 2018 at 13:05, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>>> On 28 October 2018 at 03:49, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>> I've registered as a reviewer. I still didn't have a deep look at
>> the patch yet, but thanks a lot for working on it!
>
> Thanks for signing up to review. I need to send another revision of
> the patch to add a missing call to truncate_useless_pathkeys(). Will
> try to do that today.

I've attached a patch that removes the redundant pathkeys. This allows
cases like the following to work:

explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c;
QUERY PLAN
-------------------------------------------------------------
Append
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1
Index Cond: (a = 10)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2
Index Cond: (a = 10)
(5 rows)

One thing that could work but currently does not are when LIST
partitions just allow a single value, we could allow the Append to
have pathkeys even if there are no indexes. One way to do this would
be to add PathKeys to the seqscan path on the partition for supporting
partitions. However, that's adding code in another area so likely
should be another patch.

This could allow cases like:

create table bool_rp (b bool) partition by list(b);
create table bool_rp_true partition of bool_rp for values in(true);
create table bool_rp_false partition of bool_rp for values in(false);
explain (costs off) select * from bool_rp order by b;
QUERY PLAN
------------------------------------------------------------------
Append
-> Seq Scan on bool_rp_false
-> Seq Scan on bool_rp_true
(3 rows)

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