Thread: Prepared statements (PREPARE and JDBC) are a lot slower than "normal"ones.
We are seeing a quite heavy slow down when using prepared statements in 10.1. I haven't done some thorough testing, to be honest, but what we are having is a select from a view (complexity of it should not matter in my opinion), something like this: prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and COLUMNB = $2; -- Actual test code follows. -- Takes ~2 seconds. select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35; -- Takes ~10 seconds. execute TEST ('N', 35); Both return the same amount of rows, order of execution does not matter, these times are reproducible. If the same select statement is executed through JDBC it takes roughly 6 seconds (execution time only, no data fetched at that point). I'm a little bit at a loss here. Is such a slow down "expected", did we simply miss that prepared statements are slower? Or is there something else going on that we are simply not aware of?
Re: Prepared statements (PREPARE and JDBC) are a lot slower than"normal" ones.
From
Laurenz Albe
Date:
Robert Zenz wrote: > We are seeing a quite heavy slow down when using prepared statements in 10.1. > > I haven't done some thorough testing, to be honest, but what we are having is a > select from a view (complexity of it should not matter in my opinion), something > like this: > > prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and > COLUMNB = $2; > > -- Actual test code follows. > > -- Takes ~2 seconds. > select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35; > > -- Takes ~10 seconds. > execute TEST ('N', 35); > > Both return the same amount of rows, order of execution does not matter, these > times are reproducible. If the same select statement is executed through JDBC it > takes roughly 6 seconds (execution time only, no data fetched at that point). > I'm a little bit at a loss here. Is such a slow down "expected", did we simply > miss that prepared statements are slower? Or is there something else going on > that we are simply not aware of? Most likely, you are seeing the effects of a generic plan being used. During the first five executions, the prepared statement will run a "custom plan" generated with the actual parameters. If the cost estimate of these plans is not cheaper than the cost estimate of the generic plan (without substituting the actual parameters), the generic plan will be used from the sixth execution on. You can compare the execution plans generated with EXPLAIN (ANALYZE, BUFFERS) EXECUTE test ('N', 35); and EXPLAIN (ANALYZE, BUFFERS) select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35; Yours, Laurenz Albe