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
