Re: often PREPARE can generate high load (and sometimes minutes long unavailability)

On Sun, Feb 23, 2014 at 11:04 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> 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
>

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.

Cheers,

Jeff