model-writtenrun 4239 - may be wrong, links go to the source messages. Commit-by-commit LLM analysis is in the Commits dialog.
Dec 2022 - Feb 2023 · 18 entries
- Ankit Kumar Pandey opened the thread by questioning whether the window-ordering TODO was already satisfied, demonstrating existing sort reuse and index use in #1; David Rowley explained that fully optimizing the initial window order could require prohibitively repeated join searches, which Ankit agreed to leave aside in #2 and #3.
- Ankit's examples in #4 led David to identify an undocumented dependency on reverse tleSortGroupRef ordering and parser assignment order; David demonstrated that existing tests did not protect this behavior and requested regression coverage in #5.
- Ankit supplied tests and a comment in #7, while separately proposing that sorting additional columns below WindowAgg could eliminate a later incremental sort in #6 and #8.
- Vik Fearing warned that changing a window's logical ORDER BY could change rank results in #9, and Tom Lane distinguished physical input ordering from peer-group semantics in #11; David settled on adjusting sort pathkeys without changing WindowClause.orderClause in #14.
- Tom corrected the proposed WHERE-filtering concern in #12, and David identified WindowClause runConditions as the relevant source of row reduction in #13, establishing a reason to avoid unconditional pushdown.
- Ankit posted the first pushdown implementation in #16; independently, David committed the regression-test work from #7 as a14a583292, rewriting tests around empsalary and moving the comment to the comparator, as reported in #17.
- David replaced the initial pushdown implementation with canonical pathkey comparisons in #18, guarding against window expressions in ORDER BY, existing sorted input, DISTINCT, LIMIT, and runConditions; Ankit's measurements in #22 supported retaining the LIMIT restriction.
- Ankit's tests exposed missed optimization across multiple WindowAggs in #23 and #24; David proposed a backward scan to find the deepest compatible window in #26 and #27, and the revisions through #40 refined that scan, presorted-input handling, comments, and regression coverage.
- Ankit's attempt to propagate a sort bound through WindowAgg failed with a bounded-sort error in #34, and David judged the additional machinery potentially too complex in #37; merging distinct window definitions was also left outside this patch in #36.
- David reconsidered DISTINCT support and sort-cost accuracy in #41, with Tom outlining costing difficulties in #42; David subsequently explained why DISTINCT involving window results still needs additional processing and proposed reusing differently ordered pathkeys as separate work in #45 and #48.
- Ankit combined DISTINCT pathkey reordering and IndexPath changes with window pushdown in #50, although the attached diff was reversed; David warned about expanding join-search paths and requested a separate thread in #51, which Ankit accepted in #52.
- David returned to the narrower pushdown patch in #53, replacing an unsafe reverse-list macro with a manual loop, correcting the top-level runCondition check, and improving tests; he considered it ready to commit pending objections.
- Ankit then reported an eight-column workload that became slower in #54, prompting David's independent diagnostic patch in #55, which demonstrated that Sort followed by Incremental Sort could outperform one full sort and raised possible CPU-cache effects.
- Further benchmarks from Ankit in #57 found no additional consistently bad cases, but David's broader tests in #58 showed a substantial ten-million-row regression; David added a testing GUC and parked pushdown pending investigation of duplicate key comparisons and cache behavior.
- Ankit agreed to the pause in #59 and supplied more disk-spilling benchmarks in #62; John Naylor connected related sort-specialization work in #60 and cautioned that cache-oriented changes required broad regression testing in #63.
- John brought a comparator-splitting patch from another thread into this one in #65, avoiding repeated comparison of the leading key and initially reporting that it eliminated several pushdown regressions; David suggested naming the new callbacks tiebreak in #66.
- John's follow-up measurements in #67, #69, and #70 showed standalone benefits from avoiding duplicate comparisons but persistent pushdown regressions, weakening the initial conclusion that comparator rechecks explained the problem.
- David supplied a separate cache-aware batching experiment in #71, but it was slower because the final sort still processed the entire array; he proposed merging presorted batches instead, and John supported exploring that direction in #72.
Jun - Aug 2023 · 3 entries
- After a four-month gap, John resumed the comparator work with the renamed tiebreak version in #73; David reviewed and benchmarked it in #74, finding it worthwhile independently while concluding that the remaining regressions still prevented adopting window-sort pushdown.
- John finalized #73 by adding a defensive hash-index tiebreak stub, removing an obsolete comment, and formatting the code, then committed c9bfa40914 and announced completion in #75.
- At the close of the thread, the regression tests and comparator optimization had landed, while window-sort pushdown remained parked over the performance concerns in #74; DISTINCT reordering had been separated in #52, and the cache-aware experiment had no completed successor in this thread.