Hi While working on https://commitfest.postgresql.org/patch/7302/, I noticed another potential optimization, so I'm starting a separate thread to discuss it.
I've been looking into potential optimizations in mergejoinscansel() and would appreciate feedback on whether this approach is worth pursuing. 1. Potential optimization In mergejoinscansel(), when left and right are base simple Vars referring to the same attribute of the same base relation, it may be possible to avoid the second call to examine_variable() and reuse the first VariableStatData. Furthermore, under suitably strict conditions, the second calls to get_variable_range() and scalarineqsel() may also be avoidable by reusing the results of the corresponding first calls. My initial focus is on this straightforward base-simple-Var case, where the equivalence conditions are relatively easy to establish. 2. Motivation The relevant call stack is: initial_cost_mergejoin() --> cached_scansel() --> mergejoinscansel() In initial_cost_mergejoin(), the comments for non-full joins with merge clauses describe mergejoinscansel() as a fairly expensive computation and explain that its results are cached in the merge clause's RestrictInfo. This caching avoids repeating the computation for the same merge clause. However, there may still be opportunities to eliminate redundant work within an individual call to mergejoinscansel(). 3. A common use case Consider the following query: ```SQL SELECT a.reltype FROM pg_class_1 a, pg_class_1 b WHERE a.reltype = b.reltype; Equality conditions between corresponding columns of two aliases of the same table are a common query pattern. I would like to explore whether optimizing the internal calls to examine_variable(), get_variable_range() and scalarineqsel() can further reduce planning overhead in such cases. 4. Preliminary implementation and performance results I've put together a small proof of concept targeting the case where both operands are simple Vars, and performed preliminary performance measurements. I added timing output to mergejoinscansel() to measure its contribution directly. The execution plans produced by the planner were identical between master and the patch. The following results were obtained from 128 consecutive measurements for each case: | Metric | Master | Patch | Improvement | | mergejoinscansel() | 0.117 ms | 0.064 ms | 45.03% | | Planning Time | 1.880 ms | 1.835 ms | 2.39% | For each set of 128 measurements, I excluded the first measurement, removed the maximum and minimum values from the remaining measurements, and calculated the arithmetic mean of the rest. DEBUG instrumentation also confirmed that, in this base-simple-Var case, the number of calls to each of the targeted functions (examine_variable(), get_variable_range() and scalarineqsel()) was reduced by approximately half. The reduction in mergejoinscansel() execution time is consistent with the reduced number of function calls, while the overall planning-time improvement is smaller. 5. Further cases The current implementation focuses on direct base-Var operands. Other cases may offer additionaloptimization opportunities, but they could be more complicated and would need careful analysis. For example: ```SQL SELECT oid FROM pg_catalog.pg_class WHERE oid IN (SELECT oid FROM pg_catalog.pg_class LIMIT 1); I have not yet investigated these cases in detail. I've attached the SQL and shell scripts used for testing (not_same_var.sql, not_same_var.sh, same_var.sql and same_var.sh), along with base_var_perf_test_data_analyze.xlsx, which includes the shell commands used to extract the performance metrics. I'd appreciate feedback on the approach, particularly on the correctness of the proposed reuse conditions, other cases worth considering, and whether the measured improvement justifies pursuing this optimization. If others find this direction promising, I'll continue investigating it and work on a more complete patch. Thanks for your time and feedback. regards, -- ZizhuanLiu (X-MAN) [email protected]
v1-0001-Optimize-mergejoinscansel-by-avoiding-redundant-c.patch
Description: Binary data
v1-0001-Optimize-mergejoinscansel-by-avoiding-redundant-c.patch.debug
Description: Binary data
not_same_var.sql
Description: Binary data
same_var.sql
Description: Binary data
not_same_var.s
Description: Binary data
same_var.s
Description: Binary data
base_var_perf_test_data_analyze.xlsx
Description: Binary data
