On Fri, Mar 14, 2025 at 5:36 PM Amit Langote <amitlangot...@gmail.com> wrote:
> > Thanks for the patch and the extensive benchmarking. > > Would you please also share a simple, self-contained example that I > can use to reproduce and verify the performance improvements? It’s > helpful to first see the patch in action with a representative query, > and then refer to the more extensive benchmark results you've already > shared as needed. I'm also having a hard time telling from the scripts > which query was used to produce the numbers in the report. > Here are steps 1. Run setup.sql attached in [1]. It will add a few helper functions and create required tables (partitioned and non-partitioned ones). 2. The sheet named "rae data" attached to [1] has queries whose performance is measured. They use the tables created by setup.sql. You may further use the same scripts to run benchmarks. > Btw, in the commit message, you mention: > > === > When there are thousands of partitions in a partitioned table, there > can be thousands of derived clauses in the list making it inefficient > for a lookup. > === > > I haven’t found a description of how that many clauses end up in the > ec->ec_derived list. IIUC, it's create_join_clause() where the child > clauses get added, and it would be helpful to mention that, since that > also appears to be the hotspot your patch is addressing. you are right. create_join_clause() adds the derived clauses for partitions. Please note that the optimization, being modelled after join rel list, is applicable to partitioned, non-partititoned cases as well as with or without partitionwise join. > > > I think the first patch in the attached set is worth committing, just > > to tighten things up. > > I agree and am happy to commit it if there are no objections. Thanks. [1] https://www.postgresql.org/message-id/CAExHW5vnwgTgfsCiNM7E4TnkxD1b_ZHPafNe1f041u=o131...@mail.gmail.com -- Best Wishes, Ashutosh Bapat
