Re: Ordered Partitioned Table Scans

From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
To: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Cc: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Ordered Partitioned Table Scans
Date: 2018-10-31 06:36:06
Message-ID: CAKJS1f9ocCUp-5PMU+GdMujXqF81UAqq+Ss-D8GfFE8HKvns4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v3-0001-Allow-Append-to-be-used-in-place-of-MergeAppend-f.patch application/octet-stream 49.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2018-10-31 07:19:35 syntax error: VACUUM ANALYZE VERBOSE (PostgreSQL 11 regression)
Previous Message Amit Langote 2018-10-31 06:34:02 Re: ToDo: show size of partitioned table