On Sat, May 3, 2025 at 1:18 AM Bruce Momjian <br...@momjian.us> wrote: > On Fri, May 2, 2025 at 01:00:57PM +0900, Amit Langote wrote: > > 1. Speed up execution of cached plans by deferring locks on partitions > > subject to pruning (Amit Langote) > > (bb3ec16e1, d47cbf474, cbc127917, 525392d57) > > > > 2. Speed up child EquivalenceMember lookup in planner (Yuya Watari, > > David Rowley) > > (d69d45a5a) > > > > 3. Speed up derived clause lookup in EquivalenceClass (Ashutosh Bapat) > > (88f55bc97) > > > > Alternatively, 2 and 3 can be combined as: > > > > 2. Speed up partition planning by improving EquivalenceClass lookups > > (Yuya Watari, David Rowley, Ashutosh Bapat) > > > > I think 1 should go under Partitioning, which I see is currently missing. > > > > Any thoughts, David? > > > > Can work on a patch if you'd like. > > So, a few things. First, these set of commits was in a group of 10 that > I added since there have been complaints in the past that optimizer > improvements were not listed and therefore patch authors were not given > sufficient credit. That means the 209 item count for PG 18 is 10 higher > than my normal filtering would produce. > > Second, looking at the items, these are a case of "X is faster", which > we don't normally mention in the release notes. We normally mention > "faster" when it is so much faster that use cases which were not > possible before might be possible now, so it is recommended to retest. > That is what I saw this grouped item as, whereas I don't think the > individual items meet that criteria. > > Also, I didn't see enough partition items to warrant a separate > partition section, and we didn't have one in PG 17 either. We could > pull all the partition items from the sections they are already in, but > they seem more natural in the sections they are in. > > I don't think most people would know what EquivalenceMember is, and even > if they did, would they be able to connect it to an SQL query?
Thanks for splitting these (cf847d634). I think the text for the locking item should mention “during execution,” as David also suggested. Again, I don’t think this change belongs under Optimizer since it doesn’t really affect the planner -- it’s mainly an executor improvement. Maybe the General Performance section is a better fit. Also, just to clarify why the individual items are meaningful performance improvements: * Locking change: Executing cached plans involving hundreds or thousands of partitions was bottlenecked by locking; with this change, execution is now roughly 20x faster with 1000 partitions. * Planning time improvements: Planning certain commonly used queries against partitioned tables when they don’t use partition pruning is now roughly 20x faster with 1000 partitions. -- Thanks, Amit Langote
