Re: [HACKERS] advanced partition matching algorithm for partition-wise join

Amit-san,

On Wed, Jul 31, 2019 at 2:47 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> On Tue, Jul 30, 2019 at 6:00 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> > On Fri, Jul 19, 2019 at 10:44 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > > I don't know whether partition_bounds_merge() is well-implemented; I
> > > haven't looked.
> >
> > My concern about that is list partitioning. In that case that
> > function calls partition_list_bounds_merge(), which generates the
> > partition bounds for a join relation between two given input
> > relations, by performing merge join for a pair of the datums arrays
> > from both the input relations.
>
> I had similar thoughts upon seeing that partition_bounds_merge() will
> be replacing the current way of determining if partition-wise join can
> occur; that it will make the handling of currently supported cases
> more expensive.
>
> The current way is to compare the PartitionBoundInfos of joining
> relations using partition_bounds_equal(), and if equal, simply join
> the pairs of matching partitions if the join quals permit doing so.
> There's no need to do anything extra to determine which partitions to
> join with each other, because it's already established. Likewise,
> partition_bounds_merge() shouldn't to have to anything extra in that
> case. That is, for the cases that are already supported, we should
> find a way to make partition_bounds_merge() only as expensive as just
> performing partition_bounds_equals(), or maybe just slightly more.

I 100% agree on that point.

One thing that was unexpected to me is this:

I wrote:
> To see that, I did simple
> tests using a list-partitioned table pt created with the attached,
> which has 10 partitions, each with 1000 list values, so ndatums is
> 10000. (The tests below are performed with
> enable_partitionwise_join=on.)
>
> * 2-way self-join of pt: explain analyze select * from pt t0, pt t1
> where t0.a = t1.a;
> - HEAD:
> Planning Time: 1.731 ms
> Execution Time: 15.159 ms
> - Patched:
> Planning Time: 1.884 ms
> Execution Time: 15.127 ms

IIUC, in this test, I think partition_bounds_equals() and
partition_bounds_merge() have been performed only once in HEAD and the
patched version respectively to plan the partitionwise join, so this
might imply that the cost of the latter is just slightly more
expensive than that of the former. I'm missing something, though.

Anyway I'll continue to review this patch, so I'll move this to the
next CF with the same status (Needs review).

Best regards,
Etsuro Fujita