Horton
Menu

Re: apply_scanjoin_target_to_paths and partitionwise join

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Zhihong Yu <zyu@yugabyte.com>, arne.roland@malkut.net, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Julien Rouhaud <rjuju123@gmail.com>, Etsuro Fujita <etsuro.fujita@gmail.com>
Date: 2024-07-24T04:12:13Z
Lists: pgsql-hackers 
On Wed, May 22, 2024 at 3:57 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> I will create patches for the back-branches once the patch for master is in a committable state.

AFAIU, this patch prevents apply_scanjoin_target_to_paths() from
discarding old paths of partitioned joinrels.  Therefore, we can
retain non-partitionwise join paths if the cheapest path happens to be
among them.

One concern from me is that if the cheapest path of a joinrel is a
partitionwise join path, following this approach could lead to
undesirable cross-platform plan variations, as detailed in the
original comment.

Is there a specific query that demonstrates benefits from this change?
I'm curious about scenarios where a partitionwise join runs slower
than a non-partitionwise join.

Thanks
Richard

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove redundant SET enable_partitionwise_join = on.

  2. Don't reset the pathlist of partitioned joinrels.

  3. Allow left join removals and unique joins on partitioned tables

  4. Consider fractional paths in generate_orderedappend_paths

  5. Fix handling of targetlist SRFs when scan/join relation is known empty.