RE: Speed up transaction completion faster after many relations are accessed in a transaction

From: David Rowley [mailto:david(dot)rowley(at)2ndquadrant(dot)com]
> On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> > Perhaps "speeding up planning with partitions" needs to be accepted first?
>
> Yeah, I think it likely will require that patch to be able to measure
> the gains from this patch.
>
> If planning a SELECT to a partitioned table with a large number of
> partitions using PREPAREd statements, when we attempt the generic plan
> on the 6th execution, it does cause the local lock table to expand to
> fit all the locks for each partition. This does cause the
> LockReleaseAll() to become slow due to the hash_seq_search having to
> skip over many empty buckets. Since generating a custom plan for a
> partitioned table with many partitions is still slow in master, then I
> very much imagine you'll struggle to see the gains brought by this
> patch.

Thank you David for explaining. Although I may not understand the effect of "speeding up planning with partitions" patch, this patch takes effect even without it. That is, perform the following in the same session:

1. SELECT count(*) FROM table; on a table with many partitions. That bloats the LocalLockHash.
2. PREPARE a point query, e.g., SELECT * FROM table WHERE pkey = $1;
3. EXECUTE the PREPAREd query repeatedly, with each EXECUTE in a separate transaction. Without the patch, each transaction's LockReleaseAll() has to scan the bloated large hash table.

Regards
Takayuki Tsunakawa