Re: [HACKERS] path toward faster partition pruning

Hi Amit,

On 11/24/2017 12:00 AM, Amit Langote wrote:
>> On 2017/11/23 3:56, Jesper Pedersen wrote:
>> EXPLAIN (ANALYZE) SELECT t1.a, t1.b, t2.c, t2.d FROM t1 INNER JOIN t2 ON
>> t2.c = t1.b WHERE t2.d = 1;
>>
>> I just wanted to highlight that the "JOIN ON" partition isn't pruned - the
>> "WHERE" one is.
>
> Did you mean to write ON t2.d = t1.b? If so, equivalence class mechanism
> will give rise to a t1.b = 1 and hence help prune t1's partition as well:
>

No, I meant 't2.c = t1.b'. If you take the same example, but don't
partition you will get the following plan:

test=# EXPLAIN (COSTS OFF) SELECT t1.a, t1.b, t2.c, t2.d FROM t1 INNER
JOIN t2 ON t2.c = t1.b WHERE t2.d = 1;
QUERY PLAN
----------------------------------------------
Nested Loop
-> Index Scan using idx_t2_d on t2
Index Cond: (d = 1)
-> Index Only Scan using idx_t1_b_a on t1
Index Cond: (b = t2.c)
(5 rows)

Maybe "5.10.2. Declarative Partitioning" could be expanded to include
some general "guidelines" of where partition based plans should be
checked against their non-partition counterparts (at least the first
bullet in 5.10.1 says ".. in certain situations .."). Probably a
separate patch from this.

[snip]

>> Should pruning of partitions for UPDATEs (where the partition key isn't
>> updated) and DELETEs be added to the TODO list?
>
> Note that partition pruning *does* work for UPDATE and DELETE, but only if
> you use list/range partitioning. The reason it doesn't work in this case
> (t1 is hash partitioned) is that the pruning is still based on constraint
> exclusion in the UPDATE/DELETE case and constraint exclusion cannot handle
> hash partitioning.
>

Thanks for your description.

>
> I can see how that seems a bit odd. If you use hash partitioning,
> UPDATE/DELETE do not benefit from partition-pruning, even though SELECT
> does. That's because SELECT uses the new partition-pruning method (this
> patch set) which supports hash partitioning, whereas UPDATE and DELETE use
> constraint exclusion which doesn't. It would be a good idea to make even
> UPDATE and DELETE use the new method thus bringing everyone on the same
> page, but that requires us to make some pretty non-trivial changes to how
> UPDATE/DELETE planning works for inheritance/partitioned tables, which we
> should undertake separately, imho.
>

Agreed.

Best regards,
Jesper