I'm currently facing an issue with some selects by primary key. The selects sometimes take more than 10 seconds to return the row. I've analyzed the execution plan and verified that it's ok.
I started to look the queries running on the database with the following select:
select wait_event_type, wait_event, count(1)
from pg_stat_activity
where state <> ‘idle’
and state is not null
group by wait_event_type, wait_event
order by count(1) desc;
Then I saw a lot of sessions waiting in lock_manager: wait_event_type / wait_event by count
Then, I went to the pg_locks to see what type of lock was causing the issue: locks by mode The most common lock mode was AccessShareLock. I also saw the average lock by pid, and it was about 3000. Most of the locks are in partitioned tables (my select is going through the main table, and the AccessShareLock's are in all partitions and partition indexes). I increased the max_locks_per_transaction from 128 to 5120, but had no improvements.
Another thing that I do, was run perf top and see what was the "hot function": hash_search_with_hash_value. perf top
The server machine is with a low cpu usage: 20%.
Do you have any solution for this? Do you need some more information?
First thing would be to upgrade from v11 to (at least) v12. Partitioning got much more efficient, in turns of locking for single-partition selects, in that version.