Hi David, On Fri, Mar 28, 2025 at 8:32 AM David Rowley <dgrowle...@gmail.com> wrote: > > Performing lookups on an appropriately sized hash table is going to > perform better than lookups on a sparse table. The reason for this is > that hash table probes rarely ever have a predictable memory access > pattern, and the larger the bucket array is, the more chance of having > a backend stall while fetching cache lines from some higher cache > level or RAM. So, IMO, using 256, you're leaving performance on the > table and paying in RAM for the privilege.
I don't know much about cache lines but searching for cachelines shows that they are 64 or 128 bytes long. The hash entry here is 40 bytes long, so at most 1 or 3 entries would fit in a cache line. My understanding could be wrong but it seems that we will incur a cache line fault almost for every entry that we fetch even if we fetch the entries in a tight loop. If we are performing other operations in-between like what will happen with the patch, the cache lines are going to fault anyway. So it seems the size of the hash table or its sparseness wouldn't affect timing much. Please correct me if I am wrong. > > You might not be too concerned about the memory because you've done > the tests, but testing with one EC and calling it good seems naive to > me. I recall one query that Tom posted when I was working on the EC > index stuff for 3373c7155 that had over 1000 EquivalenceClasses. I > don't know how many of those would have had > 32 ec_derives entries, > but check [1] if you want to see. Comparing root->join_rel_hash with EC->ec_derives_hash in the context of initial hash table size is a thinko on my part. It's less likely that there will be 1000 subqueries (requiring 1000 PlannerInfos) each with more than 32 join rels than a query with 1000 equivalence classes in one PlannerInfo with more than 32 ec_derives. So if using a small initial hash table doesn't impact performance negatively, why not save some memory. Thinking more about it, we know the size of ec_derives_list when creating the hash table and we are using simplehash which uses its own fillfactor and its own logic to expand the hash table, I think we should just use the length of ec_derives_list as the initial size. What do you think? -- Best Wishes, Ashutosh Bapat
