Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning

Hi David,

On Fri, Mar 28, 2025 at 8:32 AM David Rowley <dgrowleyml@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