Обсуждение: Introduce join_info_array for direct lookups of SpecialJoinInfo by ojrelid
When working on the improper qual pushdown issue [1], there is a need in
the proposed fix to avoid scanning all the SpecialJoinInfos, since that
is too expensive. I think this might be a common requirement. In the
current codes there are several places where we need to scan all the
SpecialJoinInfos in join_info_list looking for SpecialJoinInfos that
belong to a given outer join relid set, which is an O(n) operation. So
start a new thread for this requirement.
To improve the O(n) operation, introduce join_info_array to allow direct
lookups of SpecialJoinInfo by ojrelid. This is doable because for each
non-zero ojrelid there can only be one SpecialJoinInfo. This can
benefit clause_is_computable_at() and have_unsafe_outer_join_ref(), as
the patch does, and more future usages such as
add_outer_joins_to_relids() in the proposed patch for issue [1].
[1] https://www.postgresql.org/message-id/flat/0b819232-4b50-f245-1c7d-c8c61bf41827%40postgrespro.ru
Thanks
Richard
the proposed fix to avoid scanning all the SpecialJoinInfos, since that
is too expensive. I think this might be a common requirement. In the
current codes there are several places where we need to scan all the
SpecialJoinInfos in join_info_list looking for SpecialJoinInfos that
belong to a given outer join relid set, which is an O(n) operation. So
start a new thread for this requirement.
To improve the O(n) operation, introduce join_info_array to allow direct
lookups of SpecialJoinInfo by ojrelid. This is doable because for each
non-zero ojrelid there can only be one SpecialJoinInfo. This can
benefit clause_is_computable_at() and have_unsafe_outer_join_ref(), as
the patch does, and more future usages such as
add_outer_joins_to_relids() in the proposed patch for issue [1].
[1] https://www.postgresql.org/message-id/flat/0b819232-4b50-f245-1c7d-c8c61bf41827%40postgrespro.ru
Thanks
Richard
Вложения
On Thu, May 4, 2023 at 4:07 PM Richard Guo <guofenglinux@gmail.com> wrote:
When working on the improper qual pushdown issue [1], there is a need in
the proposed fix to avoid scanning all the SpecialJoinInfos, since that
is too expensive. I think this might be a common requirement. In the
current codes there are several places where we need to scan all the
SpecialJoinInfos in join_info_list looking for SpecialJoinInfos that
belong to a given outer join relid set, which is an O(n) operation. So
start a new thread for this requirement.
To improve the O(n) operation, introduce join_info_array to allow direct
lookups of SpecialJoinInfo by ojrelid. This is doable because for each
non-zero ojrelid there can only be one SpecialJoinInfo. This can
benefit clause_is_computable_at() and have_unsafe_outer_join_ref(), as
the patch does, and more future usages such as
add_outer_joins_to_relids() in the proposed patch for issue [1].
BTW, I just noticed that the introduction of join_info_array can also
benefit make_outerjoininfo(), check_redundant_nullability_qual() and
get_join_domain_min_rels(). So update the patch to do the changes.
I'd like to devise a test query that shows performance gain from this
patch, but I'm not sure how to do that. May need help here.
Any thoughts on this patch?
Thanks
Richard
benefit make_outerjoininfo(), check_redundant_nullability_qual() and
get_join_domain_min_rels(). So update the patch to do the changes.
I'd like to devise a test query that shows performance gain from this
patch, but I'm not sure how to do that. May need help here.
Any thoughts on this patch?
Thanks
Richard
Вложения
On Mon, May 8, 2023 at 10:30 AM Richard Guo <guofenglinux@gmail.com> wrote:
I'd like to devise a test query that shows performance gain from this
patch, but I'm not sure how to do that. May need help here.
I've been trying for some time but still haven't been able to come up
with a test case that shows the performance improvement of this patch.
My best guess is that situations that can benefit from direct lookups of
SpecialJoinInfo are pretty rare, and the related codes are not in the
critical path. So for now I think I'd better withdraw this patch to
avoid people wasting time reviewing it.
Thanks
Richard
with a test case that shows the performance improvement of this patch.
My best guess is that situations that can benefit from direct lookups of
SpecialJoinInfo are pretty rare, and the related codes are not in the
critical path. So for now I think I'd better withdraw this patch to
avoid people wasting time reviewing it.
Thanks
Richard