Hello I got a example of code, that generate relatively high load with minimal connections.
This code is +/- bad - it repeatedly generate prepare statement, but somewhere uses prepared statements as protections against SQL injections and they can use same use case. Pseudocode (I can send a test case privately): Script a: -- A,B are in RAM for i in 1 .. N loop insert into A values(); for j in 1 .. M loop insert into B values(); end loop; end loop; Script b: -- query is extremely fast - returns 0 or 1 rows usually 40 threads execute while true loop pr = PREPARE SELECT * FROM A LEFT JOIN B ON .. EXECUTE pr(...) sleep(10 ms) end loop running both script together can produce high load with minimal number of executed queries. 354246.00 93.0% s_lock /usr/lib/postgresql/9.2/bin/postgres 10503.00 2.8% LWLockRelease /usr/lib/postgresql/9.2/bin/postgres 8802.00 2.3% LWLockAcquire /usr/lib/postgresql/9.2/bin/postgres 828.00 0.2% _raw_spin_lock [kernel.kallsyms] 559.00 0.1% _raw_spin_lock_irqsave [kernel.kallsyms] 340.00 0.1% switch_mm [kernel.kallsyms] 305.00 0.1% poll_schedule_timeout [kernel.kallsyms] 274.00 0.1% native_write_msr_safe [kernel.kallsyms] 257.00 0.1% _raw_spin_lock_irq [kernel.kallsyms] 238.00 0.1% apic_timer_interrupt [kernel.kallsyms] 236.00 0.1% __schedule [kernel.kallsyms] 213.00 0.1% HeapTupleSatisfiesMVCC With systemtap I got list of spin locks light weight locks lockname mode count avg (time) DynamicLocks Exclusive 2804 1025 DynamicLocks Shared 106 130 ProcArrayLock Exclusive 63 963551 ProcArrayLock Shared 50 4160 LockMgrLocks Exclusive 18 159 IndividualLock Exclusive 2 7 There is relative few very long ProcArrayLocks lwlocks This issue is very pathologic on fast computers with more than 8 CPU. This issue was detected after migration from 8.4 to 9.2. (but tested with same result on 9.0) I see it on devel 9.4 today actualized. When I moved PREPARE from cycle, then described issues is gone. But when I use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is related to planner, ... Regards Pavel