Re: 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