Problem with default partition pruning

Hosoya-san,

On 2019/02/22 17:14, Yuzuko Hosoya wrote:

Hi,

I found the bug of default partition pruning when executing a range query.

-----
postgres=# create table test1(id int, val text) partition by range (id);
postgres=# create table test1_1 partition of test1 for values from (0) to (100);
postgres=# create table test1_2 partition of test1 for values from (150) to (200);
postgres=# create table test1_def partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.
-----

In the current implement, whether the default partition is scanned
or not is determined according to each condition of given WHERE
clause at get_matching_range_bounds(). In this example, scan_default
is set true according to id > 0 because id >= 200 matches the default
partition. Similarly, according to id < 30, scan_default is set true.
Then, these results are combined according to AND/OR at perform_pruning_combine_step().
In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at
get_matching_partitions()

Attached the patch. Any feedback is greatly appreciated.

Thank you for reporting. Can you please add this to March CF in Bugs
category so as not to lose track of this?

I will try to send review comments soon.

Regards,
Amit

Amit-san,

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Wednesday, February 27, 2019 11:22 AM

Hosoya-san,

On 2019/02/22 17:14, Yuzuko Hosoya wrote:

Hi,

I found the bug of default partition pruning when executing a range query.

-----
postgres=# create table test1(id int, val text) partition by range
(id); postgres=# create table test1_1 partition of test1 for values
from (0) to (100); postgres=# create table test1_2 partition of test1
for values from (150) to (200); postgres=# create table test1_def
partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.
-----

In the current implement, whether the default partition is scanned or
not is determined according to each condition of given WHERE clause at
get_matching_range_bounds(). In this example, scan_default is set
true according to id > 0 because id >= 200 matches the default
partition. Similarly, according to id < 30, scan_default is set true.
Then, these results are combined according to AND/OR at perform_pruning_combine_step().
In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at
get_matching_partitions()

Attached the patch. Any feedback is greatly appreciated.

Thank you for reporting. Can you please add this to March CF in Bugs category so as not to lose

track

of this?

I will try to send review comments soon.

Thank you for your reply. I added this to March CF.

Regards,
Yuzuko Hosoya
NTT Open Source Software Center

Hosoya-san

On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote:

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Wednesday, February 27, 2019 11:22 AM

Hosoya-san,

On 2019/02/22 17:14, Yuzuko Hosoya wrote:

Hi,

I found the bug of default partition pruning when executing a range

query.

-----
postgres=# create table test1(id int, val text) partition by range
(id); postgres=# create table test1_1 partition of test1 for values
from (0) to (100); postgres=# create table test1_2 partition of
test1 for values from (150) to (200); postgres=# create table
test1_def partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.
-----

In the current implement, whether the default partition is scanned
or not is determined according to each condition of given WHERE
clause at get_matching_range_bounds(). In this example,
scan_default is set true according to id > 0 because id >= 200
matches the default partition. Similarly, according to id < 30,

scan_default is set true.

Then, these results are combined according to AND/OR at

perform_pruning_combine_step().

In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at
get_matching_partitions()

Attached the patch. Any feedback is greatly appreciated.

Thank you for reporting. Can you please add this to March CF in Bugs
category so as not to lose

track

of this?

I will try to send review comments soon.

Thank you for your reply. I added this to March CF.

I tested with simple use case and I confirmed it works correctly like below.

In case using between clause:
postgres=# create table test1(id int, val text) partition by range (id);
postgres=# create table test1_1 partition of test1 for values from (0) to (100);
postgres=# create table test1_2 partition of test1 for values from (150) to (200);
postgres=# create table test1_def partition of test1 default;

[HEAD]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))
-> Seq Scan on test1_def (cost=0.00..29.05 rows=6 width=36) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))

[patched]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Append (cost=0.00..29.08 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.004..0.005 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))

I considered about another use case. If default partition contains rows whose id = 300 and then we add another partition which have constraints like id >= 300 and id < 400, I thought we won't scan the rows anymore. But I noticed we simply can't add such a partition.

postgres=# insert into test1 values (300);
INSERT 0 1
postgres=# create table test1_3 partition of test1 for values from (300) to (400);
ERROR: updated partition constraint for default partition "test1_def" would be violated by some row

So I haven't come up with bad cases so far :)

--
Yoshikazu Imai

Hi

Patch work fine to me, but I have one test case where default partition still scanned.

postgres=# explain select * from test1 where (id < 10) and true;
QUERY PLAN
-------------------------------------------------------------------
Append (cost=0.00..55.98 rows=846 width=36)
-> Seq Scan on test1_1 (cost=0.00..25.88 rows=423 width=36)
Filter: (id < 10)
-> Seq Scan on test1_def (cost=0.00..25.88 rows=423 width=36)
Filter: (id < 10)
(5 rows)

Hi Yuzuko Hosoya,

Ignore my last message, I think this is also a legitimate scan on default
partition.

On Mon, Mar 4, 2019 at 10:29 PM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:

Hi

Patch work fine to me, but I have one test case where default partition
still scanned.

postgres=# explain select * from test1 where (id < 10) and true;
QUERY PLAN
-------------------------------------------------------------------
Append (cost=0.00..55.98 rows=846 width=36)
-> Seq Scan on test1_1 (cost=0.00..25.88 rows=423 width=36)
Filter: (id < 10)
-> Seq Scan on test1_def (cost=0.00..25.88 rows=423 width=36)
Filter: (id < 10)
(5 rows)

--
Ibrar Ahmed

Imai-san,

Thanks for sharing your tests!

On Thu, Feb 28, 2019 at 5:27 PM Imai, Yoshikazu
<imai.yoshikazu@jp.fujitsu.com> wrote:

Hosoya-san

On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote:

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Wednesday, February 27, 2019 11:22 AM

Hosoya-san,

On 2019/02/22 17:14, Yuzuko Hosoya wrote:

Hi,

I found the bug of default partition pruning when executing a range

query.

-----
postgres=# create table test1(id int, val text) partition by range
(id); postgres=# create table test1_1 partition of test1 for values
from (0) to (100); postgres=# create table test1_2 partition of
test1 for values from (150) to (200); postgres=# create table
test1_def partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.
-----

In the current implement, whether the default partition is scanned
or not is determined according to each condition of given WHERE
clause at get_matching_range_bounds(). In this example,
scan_default is set true according to id > 0 because id >= 200
matches the default partition. Similarly, according to id < 30,

scan_default is set true.

Then, these results are combined according to AND/OR at

perform_pruning_combine_step().

In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at
get_matching_partitions()

Attached the patch. Any feedback is greatly appreciated.

Thank you for reporting. Can you please add this to March CF in Bugs
category so as not to lose

track

of this?

I will try to send review comments soon.

Thank you for your reply. I added this to March CF.

I tested with simple use case and I confirmed it works correctly like below.

In case using between clause:
postgres=# create table test1(id int, val text) partition by range (id);
postgres=# create table test1_1 partition of test1 for values from (0) to (100);
postgres=# create table test1_2 partition of test1 for values from (150) to (200);
postgres=# create table test1_def partition of test1 default;

[HEAD]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))
-> Seq Scan on test1_def (cost=0.00..29.05 rows=6 width=36) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))

[patched]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Append (cost=0.00..29.08 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.004..0.005 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))

I considered about another use case. If default partition contains rows whose id = 300 and then we add another partition which have constraints like id >= 300 and id < 400, I thought we won't scan the rows anymore. But I noticed we simply can't add such a partition.

postgres=# insert into test1 values (300);
INSERT 0 1
postgres=# create table test1_3 partition of test1 for values from (300) to (400);
ERROR: updated partition constraint for default partition "test1_def" would be violated by some row

So I haven't come up with bad cases so far :)

I didn't test cases you mentioned.
Thanks to you, I could check correctness of the patch!

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

Hi Ibrar,

On Tue, Mar 5, 2019 at 2:37 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:

Hi Yuzuko Hosoya,

Ignore my last message, I think this is also a legitimate scan on default partition.

Oh, I got it. Thanks a lot.

On Mon, Mar 4, 2019 at 10:29 PM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:

Hi

Patch work fine to me, but I have one test case where default partition still scanned.

postgres=# explain select * from test1 where (id < 10) and true;
QUERY PLAN
-------------------------------------------------------------------
Append (cost=0.00..55.98 rows=846 width=36)
-> Seq Scan on test1_1 (cost=0.00..25.88 rows=423 width=36)
Filter: (id < 10)
-> Seq Scan on test1_def (cost=0.00..25.88 rows=423 width=36)
Filter: (id < 10)
(5 rows)

--
Ibrar Ahmed

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

Le 28/02/2019 à 09:26, Imai, Yoshikazu a écrit :

Hosoya-san

On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote:

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Wednesday, February 27, 2019 11:22 AM

Hosoya-san,

On 2019/02/22 17:14, Yuzuko Hosoya wrote:

Hi,

I found the bug of default partition pruning when executing a range

query.

-----
postgres=# create table test1(id int, val text) partition by range
(id); postgres=# create table test1_1 partition of test1 for values
from (0) to (100); postgres=# create table test1_2 partition of
test1 for values from (150) to (200); postgres=# create table
test1_def partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.
-----

In the current implement, whether the default partition is scanned
or not is determined according to each condition of given WHERE
clause at get_matching_range_bounds(). In this example,
scan_default is set true according to id > 0 because id >= 200
matches the default partition. Similarly, according to id < 30,

scan_default is set true.

Then, these results are combined according to AND/OR at

perform_pruning_combine_step().

In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at
get_matching_partitions()

Attached the patch. Any feedback is greatly appreciated.

Thank you for reporting. Can you please add this to March CF in Bugs
category so as not to lose

track

of this?

I will try to send review comments soon.

Thank you for your reply. I added this to March CF.

I tested with simple use case and I confirmed it works correctly like below.

In case using between clause:
postgres=# create table test1(id int, val text) partition by range (id);
postgres=# create table test1_1 partition of test1 for values from (0) to (100);
postgres=# create table test1_2 partition of test1 for values from (150) to (200);
postgres=# create table test1_def partition of test1 default;

[HEAD]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))
-> Seq Scan on test1_def (cost=0.00..29.05 rows=6 width=36) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))

[patched]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Append (cost=0.00..29.08 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.004..0.005 rows=0 loops=1)
Filter: ((id >= 0) AND (id <= 50))

I considered about another use case. If default partition contains rows whose id = 300 and then we add another partition which have constraints like id >= 300 and id < 400, I thought we won't scan the rows anymore. But I noticed we simply can't add such a partition.

postgres=# insert into test1 values (300);
INSERT 0 1
postgres=# create table test1_3 partition of test1 for values from (300) to (400);
ERROR: updated partition constraint for default partition "test1_def" would be violated by some row

So I haven't come up with bad cases so far :)

--
Yoshikazu Imai

Hello Yoshikazu-San,

I tested your patch using some sub-partitions and found a possible problem.

I create a new partitioned partition test1_3 with 2 sub-partitions :

-------------------------

create table test1_3 partition of test1 for values from (200) to (400)
partition by range (id);
create table test1_3_1 partition of test1_3 for values from (200) to (250);
create table test1_3_2 partition of test1_3 for values from (250) to (350);

# explain select * from test1 where (id > 0 and id < 30);
                          QUERY PLAN                          
---------------------------------------------------------------
 Append  (cost=0.00..29.08 rows=6 width=36)
   ->  Seq Scan on test1_1  (cost=0.00..29.05 rows=6 width=36)
         Filter: ((id > 0) AND (id < 30))
(3 rows)

# explain select * from test1 where (id > 220 and id < 230);
                           QUERY PLAN                           
-----------------------------------------------------------------
 Append  (cost=0.00..29.08 rows=6 width=36)
   ->  Seq Scan on test1_3_1  (cost=0.00..29.05 rows=6 width=36)
         Filter: ((id > 220) AND (id < 230))
(3 rows)

# explain select * from test1
where (id > 0 and id < 30) or (id > 220 and id < 230);
                                QUERY PLAN                                
---------------------------------------------------------------------------
 Append  (cost=0.00..106.40 rows=39 width=36)
   ->  Seq Scan on test1_1  (cost=0.00..35.40 rows=13 width=36)
         Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
   ->  Seq Scan on test1_3_1  (cost=0.00..35.40 rows=13 width=36)
         Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
   ->  Seq Scan on test1_3_2  (cost=0.00..35.40 rows=13 width=36)
         Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
(7 rows)

-----------------

Partition pruning is functioning when only the sub-partition is
required. When both the partition and the sub-partition is required,
there is no pruning on the sub-partition.

Cordialement,

--
Thibaut Madelaine
Dalibo

Hi Thibaut,

Thanks a lot for your test and comments.

Le 28/02/2019 à 09:26, Imai, Yoshikazu a écrit :

Hosoya-san

On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote:

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Wednesday, February 27, 2019 11:22 AM

Hosoya-san,

On 2019/02/22 17:14, Yuzuko Hosoya wrote:

Hi,

I found the bug of default partition pruning when executing a range

query.

-----
postgres=# create table test1(id int, val text) partition by range
(id); postgres=# create table test1_1 partition of test1 for values
from (0) to (100); postgres=# create table test1_2 partition of
test1 for values from (150) to (200); postgres=# create table
test1_def partition of test1 default;

postgres=# explain select * from test1 where id > 0 and id < 30;
QUERY PLAN
----------------------------------------------------------------
Append (cost=0.00..11.83 rows=59 width=11)
-> Seq Scan on test1_1 (cost=0.00..5.00 rows=58 width=11)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_def (cost=0.00..6.53 rows=1 width=12)
Filter: ((id > 0) AND (id < 30))
(5 rows)

There is no need to scan the default partition, but it's scanned.
-----

In the current implement, whether the default partition is scanned
or not is determined according to each condition of given WHERE
clause at get_matching_range_bounds(). In this example,
scan_default is set true according to id > 0 because id >= 200
matches the default partition. Similarly, according to id < 30,

scan_default is set true.

Then, these results are combined according to AND/OR at

perform_pruning_combine_step().

In this case, final result's scan_default is set true.

The modifications I made are as follows:
- get_matching_range_bounds() determines only offsets of range bounds
according to each condition
- These results are combined at perform_pruning_combine_step()
- Whether the default partition is scanned or not is determined at
get_matching_partitions()

Attached the patch. Any feedback is greatly appreciated.

Thank you for reporting. Can you please add this to March CF in
Bugs category so as not to lose

track

of this?

I will try to send review comments soon.

Thank you for your reply. I added this to March CF.

I tested with simple use case and I confirmed it works correctly like below.

In case using between clause:
postgres=# create table test1(id int, val text) partition by range
(id); postgres=# create table test1_1 partition of test1 for values
from (0) to (100); postgres=# create table test1_2 partition of test1
for values from (150) to (200); postgres=# create table test1_def
partition of test1 default;

[HEAD]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
----------------------------------------------------------------------
-------------------------------------
Append (cost=0.00..58.16 rows=12 width=36) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.005..0.005

rows=0 loops=1)

Filter: ((id >= 0) AND (id <= 50))
-> Seq Scan on test1_def (cost=0.00..29.05 rows=6 width=36) (actual

time=0.002..0.002 rows=0 loops=1)

Filter: ((id >= 0) AND (id <= 50))

[patched]
postgres=# explain analyze select * from test1 where id between 0 and 50;
QUERY PLAN
----------------------------------------------------------------------
-----------------------------------
Append (cost=0.00..29.08 rows=6 width=36) (actual time=0.006..0.006 rows=0 loops=1)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36) (actual time=0.004..0.005

rows=0 loops=1)

Filter: ((id >= 0) AND (id <= 50))

I considered about another use case. If default partition contains rows whose id = 300

and then we add another partition which have constraints like id >= 300 and id < 400, I thought
we won't scan the rows anymore. But I noticed we simply can't add such a partition.

postgres=# insert into test1 values (300); INSERT 0 1 postgres=#
create table test1_3 partition of test1 for values from (300) to
(400);
ERROR: updated partition constraint for default partition "test1_def"
would be violated by some row

So I haven't come up with bad cases so far :)

--
Yoshikazu Imai

Hello Yoshikazu-San,

I tested your patch using some sub-partitions and found a possible problem.

I create a new partitioned partition test1_3 with 2 sub-partitions :

-------------------------

create table test1_3 partition of test1 for values from (200) to (400) partition by range
(id); create table test1_3_1 partition of test1_3 for values from (200) to (250); create
table test1_3_2 partition of test1_3 for values from (250) to (350);

# explain select * from test1 where (id > 0 and id < 30);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..29.08 rows=6 width=36)
-> Seq Scan on test1_1 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
(3 rows)

# explain select * from test1 where (id > 220 and id < 230);
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..29.08 rows=6 width=36)
-> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 220) AND (id < 230))
(3 rows)

# explain select * from test1
where (id > 0 and id < 30) or (id > 220 and id < 230);
QUERY PLAN
---------------------------------------------------------------------------
Append (cost=0.00..106.40 rows=39 width=36)
-> Seq Scan on test1_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
-> Seq Scan on test1_3_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
-> Seq Scan on test1_3_2 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
(7 rows)

-----------------

Partition pruning is functioning when only the sub-partition is required. When both the
partition and the sub-partition is required, there is no pruning on the sub-partition.

Indeed, it's problematic. I also did test and I found that
this problem was occurred when any partition didn't match
WHERE clauses. So following query didn't work correctly.

# explain select * from test1_3 where (id > 0 and id < 30);
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36)
-> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_3_2 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
(5 rows)

I created a new patch to handle this problem, and confirmed
the query you mentioned works as expected

# explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id < 230);
QUERY PLAN
---------------------------------------------------------------------------
Append (cost=0.00..70.93 rows=26 width=36)
-> Seq Scan on test1_1_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
-> Seq Scan on test1_3_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
(5 rows)

v2 patch attached.
Could you please check it again?

--
Best regards,
Yuzuko Hosoya

Hello.

At Fri, 15 Mar 2019 15:05:41 +0900, "Yuzuko Hosoya" <hosoya.yuzuko@lab.ntt.co.jp> wrote in <001901d4daf5$1ef4f640$5cdee2c0$@lab.ntt.co.jp>

v2 patch attached.
Could you please check it again?

I have some comments on the patch itself.

The patch relies on the fact(?) that the lowest index is always
-1 in range partition and uses it as pseudo default
partition. I'm not sure it is really the fact and anyway it
donsn't seem the right thing to do. Could you explain how it
works, not what you did in this patch?

L96:

/* There can only be zero or one matching partition. */
-                    if (partindices[off + 1] >= 0)
-                        result->bound_offsets = bms_make_singleton(off + 1);
-                    else
-                        result->scan_default =
-                            partition_bound_has_default(boundinfo);
+                    result->bound_offsets = bms_make_singleton(off + 1);

The comment had a meaning for the old code. Seems to need rewrite?

L183:

+                /*                                                                                                
+                 * All bounds are greater than the key, so we could only                                          
+                 * expect to find the lookup key in the default partition.                                        
+                 */

Long trailing spaces are attached to every line without
substantial modification.

L198:

-                 * inclusive, no need add the adjacent partition.
+                 * inclusive, no need add the adjacent partition.  If 'off' is
+                 * -1 indicating that all bounds are greater, then we simply
+                 * end up adding the first bound's offset, that is, 0.

off doesn't seem to be -1 there.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hosoya-san,

On 2019/03/15 15:05, Yuzuko Hosoya wrote:

Indeed, it's problematic. I also did test and I found that
this problem was occurred when any partition didn't match
WHERE clauses. So following query didn't work correctly.

# explain select * from test1_3 where (id > 0 and id < 30);
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36)
-> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_3_2 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
(5 rows)

I created a new patch to handle this problem, and confirmed
the query you mentioned works as expected

# explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id < 230);
QUERY PLAN
---------------------------------------------------------------------------
Append (cost=0.00..70.93 rows=26 width=36)
-> Seq Scan on test1_1_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
-> Seq Scan on test1_3_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
(5 rows)

v2 patch attached.
Could you please check it again?

I think the updated patch breaks the promise that
get_matching_range_bounds won't set scan_default based on individual
pruning value comparisons. How about the attached delta patch that
applies on top of your earlier v1 patch, which fixes the issue reported by
Thibaut?

Thanks,
Amit

Hello.

At Fri, 15 Mar 2019 17:30:07 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote in <20190315.173007.147577546.horiguchi.kyotaro@lab.ntt.co.jp>

The patch relies on the fact(?) that the lowest index is always
-1 in range partition and uses it as pseudo default
partition. I'm not sure it is really the fact and anyway it
donsn't seem the right thing to do. Could you explain how it
works, not what you did in this patch?

I understood how it works but still uneasy that only list
partitioning requires scan_default. Anyway please ignore this.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi.

At Mon, 18 Mar 2019 18:44:07 +0900, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote in <9bed6b79-f264-6976-b880-e2a5d23e9d85@lab.ntt.co.jp>

v2 patch attached.
Could you please check it again?

I think the updated patch breaks the promise that
get_matching_range_bounds won't set scan_default based on individual
pruning value comparisons. How about the attached delta patch that
applies on top of your earlier v1 patch, which fixes the issue reported by
Thibaut?

I read through the patch and understood how it works. And Amit's
proposal looks fine.

But that makes me think of scan_default as a wart.

The attached patch is a refactoring that removes scan_default
from PruneStepResult and the defaut partition is represented as
the same way as non-default partitions, without changing in
behavior. This improves the modularity of partprune code a bit.

The fix would be put on top of this easily.

Thoughts?

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hi Amit-san,

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Monday, March 18, 2019 6:44 PM

Hosoya-san,

On 2019/03/15 15:05, Yuzuko Hosoya wrote:

Indeed, it's problematic. I also did test and I found that this
problem was occurred when any partition didn't match WHERE clauses.
So following query didn't work correctly.

# explain select * from test1_3 where (id > 0 and id < 30);
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36)
-> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_3_2 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
(5 rows)

I created a new patch to handle this problem, and confirmed the query
you mentioned works as expected

# explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id < 230);
QUERY PLAN
----------------------------------------------------------------------
----- Append (cost=0.00..70.93 rows=26 width=36)
-> Seq Scan on test1_1_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
-> Seq Scan on test1_3_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id <
230)))
(5 rows)

v2 patch attached.
Could you please check it again?

I think the updated patch breaks the promise that get_matching_range_bounds won't set scan_default
based on individual pruning value comparisons. How about the attached delta patch that applies on
top of your earlier v1 patch, which fixes the issue reported by Thibaut?

Indeed. I agreed with your proposal.
Also, I confirmed your patch works correctly.

Best regards,
Yuzuko Hosoya

Le 19/03/2019 à 08:01, Yuzuko Hosoya a écrit :

Hi Amit-san,

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Monday, March 18, 2019 6:44 PM

Hosoya-san,

On 2019/03/15 15:05, Yuzuko Hosoya wrote:

Indeed, it's problematic. I also did test and I found that this
problem was occurred when any partition didn't match WHERE clauses.
So following query didn't work correctly.

# explain select * from test1_3 where (id > 0 and id < 30);
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..58.16 rows=12 width=36)
-> Seq Scan on test1_3_1 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
-> Seq Scan on test1_3_2 (cost=0.00..29.05 rows=6 width=36)
Filter: ((id > 0) AND (id < 30))
(5 rows)

I created a new patch to handle this problem, and confirmed the query
you mentioned works as expected

# explain select * from test1 where (id > 0 and id < 30) or (id > 220 and id < 230);
QUERY PLAN
----------------------------------------------------------------------
----- Append (cost=0.00..70.93 rows=26 width=36)
-> Seq Scan on test1_1_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id < 230)))
-> Seq Scan on test1_3_1 (cost=0.00..35.40 rows=13 width=36)
Filter: (((id > 0) AND (id < 30)) OR ((id > 220) AND (id <
230)))
(5 rows)

v2 patch attached.
Could you please check it again?

I think the updated patch breaks the promise that get_matching_range_bounds won't set scan_default
based on individual pruning value comparisons. How about the attached delta patch that applies on
top of your earlier v1 patch, which fixes the issue reported by Thibaut?

Indeed. I agreed with your proposal.
Also, I confirmed your patch works correctly.

Best regards,
Yuzuko Hosoya

I kept on testing with sub-partitioning.
I found a case, using 2 default partitions, where a default partition is
not pruned:

--------------

create table test2(id int, val text) partition by range (id);
create table test2_20_plus_def partition of test2 default;
create table test2_0_20 partition of test2 for values from (0) to (20)
  partition by range (id);
create table test2_0_10 partition of test2_0_20 for values from (0) to (10);
create table test2_10_20_def partition of test2_0_20 default;

# explain (costs off) select * from test2 where id=5 or id=25;
               QUERY PLAN               
-----------------------------------------
 Append
   ->  Seq Scan on test2_0_10
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_10_20_def
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_20_plus_def
         Filter: ((id = 5) OR (id = 25))
(7 rows)

--------------

I have the same output using Amit's v1-delta.patch or Hosoya's
v2_default_partition_pruning.patch.

Hi Thibaut,

On 2019/03/19 23:58, Thibaut Madelaine wrote:

I kept on testing with sub-partitioning.

Thanks.

I found a case, using 2 default partitions, where a default partition is
not pruned:

--------------

create table test2(id int, val text) partition by range (id);
create table test2_20_plus_def partition of test2 default;
create table test2_0_20 partition of test2 for values from (0) to (20)
  partition by range (id);
create table test2_0_10 partition of test2_0_20 for values from (0) to (10);
create table test2_10_20_def partition of test2_0_20 default;

# explain (costs off) select * from test2 where id=5 or id=25;
               QUERY PLAN               
-----------------------------------------
 Append
   ->  Seq Scan on test2_0_10
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_10_20_def
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_20_plus_def
         Filter: ((id = 5) OR (id = 25))
(7 rows)

--------------

I have the same output using Amit's v1-delta.patch or Hosoya's
v2_default_partition_pruning.patch.

I think I've figured what may be wrong.

Partition pruning step generation code should ignore any arguments of an
OR clause that won't be true for a sub-partitioned partition, given its
partition constraint.

In this case, id = 25 contradicts test2_0_20's partition constraint (which
is, a IS NOT NULL AND a >= 0 AND a < 20), so the OR clause should really
be simplified to id = 5, ignoring the id = 25 argument. Note that we
remove id = 25 only for the considerations of pruning and not from the
actual clause that's passed to the final plan, although it wouldn't be a
bad idea to try to do that.

Attached revised delta patch, which includes the fix described above.

Thanks,
Amit

Le 20/03/2019 à 10:06, Amit Langote a écrit :

Hi Thibaut,

On 2019/03/19 23:58, Thibaut Madelaine wrote:

I kept on testing with sub-partitioning.

Thanks.

I found a case, using 2 default partitions, where a default partition is
not pruned:

--------------

create table test2(id int, val text) partition by range (id);
create table test2_20_plus_def partition of test2 default;
create table test2_0_20 partition of test2 for values from (0) to (20)
  partition by range (id);
create table test2_0_10 partition of test2_0_20 for values from (0) to (10);
create table test2_10_20_def partition of test2_0_20 default;

# explain (costs off) select * from test2 where id=5 or id=25;
               QUERY PLAN               
-----------------------------------------
 Append
   ->  Seq Scan on test2_0_10
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_10_20_def
         Filter: ((id = 5) OR (id = 25))
   ->  Seq Scan on test2_20_plus_def
         Filter: ((id = 5) OR (id = 25))
(7 rows)

--------------

I have the same output using Amit's v1-delta.patch or Hosoya's
v2_default_partition_pruning.patch.

I think I've figured what may be wrong.

Partition pruning step generation code should ignore any arguments of an
OR clause that won't be true for a sub-partitioned partition, given its
partition constraint.

In this case, id = 25 contradicts test2_0_20's partition constraint (which
is, a IS NOT NULL AND a >= 0 AND a < 20), so the OR clause should really
be simplified to id = 5, ignoring the id = 25 argument. Note that we
remove id = 25 only for the considerations of pruning and not from the
actual clause that's passed to the final plan, although it wouldn't be a
bad idea to try to do that.

Attached revised delta patch, which includes the fix described above.

Thanks,
Amit

Amit, I tested many cases with nested range sub-partitions... and I did
not find any problem with your last patch  :-)

I tried mixing with hash partitions with no problems.

From the patch, there seems to be less checks than before. I cannot
think of a case that can have performance impacts.

Hosoya-san, if you agree with Amit's proposal, do you think you can send
a patch unifying your default_partition_pruning.patch and Amit's second
v1-delta.patch?

Cordialement,

Thibaut

Hi,

Thanks a lot for additional tests and the new patch.

Le 20/03/2019 à 10:06, Amit Langote a écrit :

Hi Thibaut,

On 2019/03/19 23:58, Thibaut Madelaine wrote:

I kept on testing with sub-partitioning.

Thanks.

I found a case, using 2 default partitions, where a default partition
is not pruned:

--------------

create table test2(id int, val text) partition by range (id); create
table test2_20_plus_def partition of test2 default; create table
test2_0_20 partition of test2 for values from (0) to (20)
partition by range (id);
create table test2_0_10 partition of test2_0_20 for values from (0)
to (10); create table test2_10_20_def partition of test2_0_20
default;

# explain (costs off) select * from test2 where id=5 or id=25;
QUERY PLAN
-----------------------------------------
Append
-> Seq Scan on test2_0_10
Filter: ((id = 5) OR (id = 25))
-> Seq Scan on test2_10_20_def
Filter: ((id = 5) OR (id = 25))
-> Seq Scan on test2_20_plus_def
Filter: ((id = 5) OR (id = 25))
(7 rows)

--------------

I have the same output using Amit's v1-delta.patch or Hosoya's
v2_default_partition_pruning.patch.

I think I've figured what may be wrong.

Partition pruning step generation code should ignore any arguments of
an OR clause that won't be true for a sub-partitioned partition, given
its partition constraint.

In this case, id = 25 contradicts test2_0_20's partition constraint
(which is, a IS NOT NULL AND a >= 0 AND a < 20), so the OR clause
should really be simplified to id = 5, ignoring the id = 25 argument.
Note that we remove id = 25 only for the considerations of pruning and
not from the actual clause that's passed to the final plan, although
it wouldn't be a bad idea to try to do that.

Attached revised delta patch, which includes the fix described above.

Thanks,
Amit

Amit, I tested many cases with nested range sub-partitions... and I did not find any problem with your
last patch :-)

I tried mixing with hash partitions with no problems.

From the patch, there seems to be less checks than before. I cannot think of a case that can have
performance impacts.

Hosoya-san, if you agree with Amit's proposal, do you think you can send a patch unifying your
default_partition_pruning.patch and Amit's second v1-delta.patch?

I understood Amit's proposal. But I think the issue Thibaut reported would
occur regardless of whether clauses have OR clauses or not as follows.
I tested a query which should output "One-Time Filter: false".

# explain select * from test2_0_20 where id = 25;
QUERY PLAN
-----------------------------------------------------------------------
Append (cost=0.00..25.91 rows=6 width=36)
-> Seq Scan on test2_10_20_def (cost=0.00..25.88 rows=6 width=36)
Filter: (id = 25)

As Amit described in the previous email, id = 25 contradicts test2_0_20's
partition constraint, so I think this clause should be ignored and we can
also handle this case in the similar way as Amit proposal.

I attached v1-delta-2.patch which fix the above issue.

What do you think about it?

Best regards,
Yuzuko Hosoya

Hosoya-san,

On 2019/03/22 15:02, Yuzuko Hosoya wrote:

I understood Amit's proposal. But I think the issue Thibaut reported would
occur regardless of whether clauses have OR clauses or not as follows.
I tested a query which should output "One-Time Filter: false".

# explain select * from test2_0_20 where id = 25;
QUERY PLAN
-----------------------------------------------------------------------
Append (cost=0.00..25.91 rows=6 width=36)
-> Seq Scan on test2_10_20_def (cost=0.00..25.88 rows=6 width=36)
Filter: (id = 25)

Good catch, thanks.

As Amit described in the previous email, id = 25 contradicts test2_0_20's
partition constraint, so I think this clause should be ignored and we can
also handle this case in the similar way as Amit proposal.

I attached v1-delta-2.patch which fix the above issue.

What do you think about it?

It looks fine to me. You put the code block to check whether a give
clause contradicts the partition constraint in its perfect place. :)

Maybe we should have two patches as we seem to be improving two things:

1. Patch to fix problems with default partition pruning originally
reported by Hosoya-san

2. Patch to determine if a given clause contradicts a sub-partitioned
table's partition constraint, fixing problems unearthed by Thibaut's tests

About the patch that Horiguchi-san proposed upthread, I think it has merit
that it will make partprune.c code easier to reason about, but I think we
should pursue it separately.

Thanks,
Amit