Re: d25ea01275 and partitionwise join

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>, Richard Guo <riguo(at)pivotal(dot)io>, Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: d25ea01275 and partitionwise join
Date: 2020-04-06 17:41:06
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> which does succeed in using partitionwise join. Please see attached
> delta that applies on your v7 if that is what you'd rather have.

I figured these queries were cheap enough that we could afford to run
both. With that and some revision of the comments (per attached),
I was feeling like we were ready to go. However, re-reading the thread,
one of Richard's comments struck me as still relevant. If you try, say,

create table p (k int, val int) partition by range(k);
create table p_1 partition of p for values from (1) to (10);
create table p_2 partition of p for values from (10) to (100);

set enable_partitionwise_join = 1;

select * from (p as t1 full join p as t2 on t1.k = t2.k) as t12(k1,val1,k2,val2)
full join p as t3 on COALESCE(t12.k1, t12.k2) = t3.k;

this patch will give you a partitioned join, with a different plan
than you get without enable_partitionwise_join. This is scary,
because it's not immediately obvious that the transformation is

I *think* that it might be all right, because although what we
are matching to is a user-written COALESCE() not an actual
FULL JOIN USING column, it has to behave in somewhat the same
way. In particular, by construction it must be a coalesce of
some representation of the matching partition columns of the
full join's inputs. So, even though it might go to null in
different cases than an actual USING variable would do, it
does not break the ability to partition the join.

However, I have not spent a whole lot of time thinking about
partitionwise joins, so rather than go ahead and commit I am
going to toss that point back out for community consideration.
At the very least, what I'd written in the comment needs a
lot more defense than it has now.


regards, tom lane

Attachment Content-Type Size
v8-0001-Fix-partitionwise-join-to-handle-FULL-JOINs-corre.patch text/x-diff 11.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2020-04-06 17:43:02 Re: Don't try fetching future segment of a TLI.
Previous Message Peter Geoghegan 2020-04-06 17:40:38 Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)