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
Digging through uncommitted tuples at the top or bottom of an index (which happenings during planning, especially the planner of merge joins) is very contentious. Tom proposed changing the snapshot used for planning to Dirty, but the proposal didn't go anywhere because no one did the testing to confirm that it solved the problem in the field. Perhaps you can help do that.
See:
"[PERFORM] Performance bug in prepared statement binding in 9.2?" and several related threads.
