Re: Reducing planning time on tables with many indexes

On 2022-Aug-19, David Geier wrote:

> Beyond that I did some off-CPU profiling to precisely track down which lock
> serializes execution. It turned out to be the MyProc::fpInfoLock lightweight
> lock. This lock is used in the fast path of the heavyweight lock. In the
> contenting case, fpInfoLock is acquired in LW_EXCLUSIVE mode to (1) check if
> there is no other process holding a stronger lock, and if not, to reserve a
> process local fast path lock slot and (2) to return the fast path lock slots
> all in one go. To do so, the current implementation always linearly iterates
> over all lock slots.

Ah, so this is the aspect that you mentioned to me today. I definitely
think that this analysis deserves its own thread, and the fix is its own
separate patch.

> I have attached the patch to improve the heavyweight lock fast path. It also
> for now contains moving out _bt_getrootheight(). For workloads where the
> same set of locks is used over and over again, it only needs on average a
> single loop iteration to find the relation (instead of a linear scan
> before). This allows to increase the number of fast path locks by a lot. In
> this patch I increased them from 16 to 64. The code can be further improved
> for cases where to be locked relations change frequently and therefore the
> chance of not finding a relation and because of that having to linearly
> search the whole array is higher.

I suggest to put each change in a separate patch:

1. improve fast-path lock algorithm to find the element, perhaps
together with increasing the number of elements in the array
2. change _bt_getrootheight

However, since patch (1) may have nontrivial performance implications,
you would also need to justify the change: not only that improves the
case where many locks are acquired, but also that it does not make the
case with few locks worse.

I strongly suggest to not include C++ comments or any other dirtiness in
the patch, as that might deter some potential reviewers.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"It takes less than 2 seconds to get to 78% complete; that's a good sign.
A few seconds later it's at 90%, but it seems to have stuck there. Did
somebody make percentages logarithmic while I wasn't looking?"
http://smylers.hates-software.com/2005/09/08/1995c749.html