Re: Introduce join_info_array for direct lookups of SpecialJoinInfo by ojrelid
On Mon, May 8, 2023 at 10:30 AM Richard Guo 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
Re: Introduce join_info_array for direct lookups of SpecialJoinInfo by ojrelid
On Thu, May 4, 2023 at 4:07 PM Richard Guo 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 v2-0001-Allow-direct-lookups-of-SpecialJoinInfo-by-ojrelid.patch Description: Binary data
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 v1-0001-Allow-direct-lookups-of-SpecialJoinInfo-by-ojrelid.patch Description: Binary data
