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