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

From: David Rowley [mailto:david.rowley@2ndquadrant.com]
> On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.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'sLockReleaseAll() has to scan the bloated large hash table.
 


Regards
Takayuki Tsunakawa