Re: [question] multil-column range partition prune

On Thu, 10 Aug 2023 at 12:16, tender wang <tndrwang(at)gmail(dot)com> wrote:
>
> I have an range partition and query below:
> create table p_range(a int, b int) partition by range (a,b); create table p_range1 partition of p_range for values from (1,1) to (3,3); create table p_range2 partition of p_range for values from (4,4) to (6,6); explain select * from p_range where b =2;
> QUERY PLAN
> --------------------------------------------------------------------------
> Append (cost=0.00..76.61 rows=22 width=8)
> -> Seq Scan on p_range1 p_range_1 (cost=0.00..38.25 rows=11 width=8)
> Filter: (b = 2)
> -> Seq Scan on p_range2 p_range_2 (cost=0.00..38.25 rows=11 width=8)
> Filter: (b = 2)
> (5 rows)
>
> The result of EXPLAIN shows that no partition prune happened.
> And gen_prune_steps_from_opexps() has comments that can answer the result.
> /*
> * For range partitioning, if we have no clauses for the current key,
> * we can't consider any later keys either, so we can stop here.
> */
> if (part_scheme->strategy == PARTITION_STRATEGY_RANGE &&
> clauselist == NIL)
> break;
>
> But I want to know why we don't prune when just have latter partition key in whereClause.
> Thanks.

Multi-column range partitioning uses row compares for range
partitions. For single columns that doesn't matter much, but for
multiple columns it is slightly less intuitive. But because they are
row compares, that means for the given partitions, the values
contained would be:

p_range1 contains rows with
- A = 1, B >= 1
- A > 1 and A < 3, B: any value
- A = 3, B < 3

p_range2 contains rows with:
- A = 4, B >= 4
- A > 4 and A < 6, B: any value
- A = 6, B < 6

As you can see, each partition contains a set of rows that may have
any value for B, and thus these partitions cannot be pruned based on
the predicate.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)