PostgreSQL Performance – Many Parallel Queries Hang Without Deadlock

The sql is a very long multi-query like select-join. All keys are indexed. When I run a load test with 70 connections continuously querying they all at some point stop receiving any network response (was Exception before I set Command Timeout = 0). I see many AccessSharedLocks started 15 minutes ago! Also there are several ExclusiveLocks for the same queries (why?). In pgAdmin this query is executed in 12 ms (at the same time!). The disk and CPU activity are both low.

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

– returns 0 rows so they are not waiting for locks.

The all queries have waiting = 'f'

How can I fix it?

(Clickable)

select user0_.id as id73_0_, user0_.nickname as nickname73_0_, ..., challenges1_.start_at as start2_78_1_, challenges1_.counter as counter78_1_, challenges1_.user_id as user5_78_1_, challenges1_.challenge_id as challenge6_78_1_, challenges1_.user_id as user5_0__, challenges1_.id as id0__ from public.users user0_ left outer join public.user_challenges challenges1_ on user0_.id=challenges1_.user_id where user0_.id in ([single id]);

SELECT this_.id as id73_4_,..., characters2_.user_id as user28_6_, characters2_.id as id6_, characters2_.id as id1_0_, ..., smartchall3_.character_id as character3_7_, smartchall3_.id as id7_, smartchall3_.id as id79_1_, ..., userupdate4_.id as id99_2_, userupdate4_.coins as coins99_2_, userupdate4_.gold as gold99_2_, ..., userupdate4_.tournament_xp as tournament10_99_2_, userupdate4_.tournament_class_xp as tournament11_99_2_, userupdate4_.tournament_xp_in_current_guild as tournament12_99_2_, userupdate4_.last_login_guild_user_leave_counter as last13_99_2_, userupdate4_.last_login_guild_user_join_counter as last14_99_2_, ..., user5_.id as id73_3_, user5_.nickname as nickname73_3_, ... FROM public.users this_ left outer join public.characters characters2_ on this_.id=characters2_.user_id left outer join public.user_character_smart_challenge_trackers smartchall3_ on characters2_.id=smartchall3_.character_id left outer join public.users_updateable_data userupdate4_ on this_.id=userupdate4_.id left outer join public.users user5_ on userupdate4_.id=user5_.id WHERE this_.id in ([single id])

Note that adding SSD somehow postpones this issue until higher load.

I use Npgsql at the client side. All client connections are just waiting at Socket.Receive.

My log settings are:

log_min_duration_statement = 1000   # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on         # log lock waits >= deadlock_timeout
#log_statement = 'none'         # none, ddl, mod, all
log_temp_files = 0 # 0 logs all temp files

The log file is here https://1drv.ms/u/s!AhAvFYRsqyUIg6tvjXHPxe8iabOFWA