Re: A problem about partitionwise join

On Fri, May 3, 2024 at 7:47 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>> Does the additional logic added by this patch have a noticeable
>> performance cost?
>
> I think one concern regarding performance cost is that the function
> exprs_known_equal() would be called O(N^2) times, where N is the number
> of partition key expressions. But I think this might not be a problem.
> The number of a joinrel's partition key expressions would only be equal
> to the join degree, since each base relation within the join contributes
> only one partition key expression, according to
> set_joinrel_partition_key_exprs(). This number would not scale with the
> number of partitions. But I have not measured the performance in
> practice by running benchmarks. Maybe I'm just wrong.

I don't know, but I do think you should do some benchmarking and see
if you can find cases where this regresses performance. In my opinion,
this feature is worth having only if it's basically free. There's lots
of things we could do in the planner that would give better (perhaps
much better) plans in certain cases, but which we don't do because in
all other cases we'd pay a certain number of CPU cycles to have them
and it just doesn't make sense given how often we'd actually get a
benefit. This might be another such case.

I agree with you that the number of partition key expressions is
likely to be small, but that doesn't mean there's no problem. A big
part of the value of equivalence classes is that they let us make
deductions cheaply. Replacing that with some more complex matching
mechanism probably has a cost, and maybe that cost is material. If
it's not quite material with one partition key expression, going up to
2 or 3 of them might make it matter.

--
Robert Haas
EDB: http://www.enterprisedb.com