On Friday 05 August 2005 11:57, Martijn van Oosterhout wrote: > On Fri, Aug 05, 2005 at 10:08:42AM +0100, David Goodenough wrote: > > I was looking at an application recently which was written in Java and > > used Postgresql as it DB. In it extensive use had been made of > > PreparedStatements both for SELECTs and for INSERT, UPDATE and DELETE > > statements. Some of the routines had multiple UPDATEs doing much the > > same thing but with slightly different parameters. In the comments it > > was stated that it was better to prepare lots of statements in advance > > rather than build one on the spot (and then prepare it, it needed the > > substitution) because of the optimiser. > > Which version of PostgreSQL was this built for? Until recently there > was no support for server side prepared statements so it mattered not > one wit whether you had one or a thousand prepared queries, it was all > done by the client anyway. I am not sure it was originally build for PostgreSQL, but it all client side anyway, or that its inside Tomcat and thus from PG's point of view client side. I presume by server side you mean triggers and functions or am I misunderstanding you? > > > This set me thinking (always dangerous). I can see how a SELECT can be > > helped by preparing the statement, but not really how an INSERT could > > or, other than the SELECT implicit in the WHERE clause on an UPDATE or > > DELETE, how UPDATE or DELETE statements would be helped. > > For the executors point of view, there is no difference between a > SELECT, INSERT, DELETE or UPDATE. Each is doing a query on the database > but doing different things with the result. SELECT sends it to the > client, UPDATE changes some values and writes the new tuple out, DELETE > marks the rows deleted. INSERT ... VALUES () has a trivial plan but > INSERT .. SELECT can be complicated. This particular application is only using INSERT ... VALUES( ) so this is in the trivial camp. I had not ever thought of DELETE and UPDATE being variants on SELECT, but it makes sense the way you explains it. > > On the client side, prepared statements simplify coding, since they > seperate the actual SQL text from the function it performs. So there > you should use one statement for each "operation" you perform, whatever > that means for your app. > > On the server side, prepared statements are a way of saving the plan of > a query and using it multiple times. So the benefit is related to how > many times you use the statement vs how complex the query is (parsing > and planning time). > > If your INSERT statement is simple, why bother with prepared stataments, > since the planning time will be almost nil anyway. If your hugely > complicated DELETE is only run once, again, no benefit since you're not > reusing the plan. > > Only in the case where you have a query which you execute a lot of > times (10, 100, 1000) is it a noticable benefit. Accordingly, several > Postgres frontends support prepared stataments, but only actually plan > them in the server if you use them more than a predefined number of > times. Understood. > > Actually, there is one downside with prepared queries. When processing > each query individually, PostgreSQL can use the statistics for the > values given to produce the optimal plan for that set. If your value > are not "equally distributed" (can't think of a better phrase) then > that plan might not be optimal for all the other substitutions you > might do. Something to think about. > > In any case, I hope this has clarified things for you. It's all a > tradeoff between code clarity, parsing, planning and execution time. > > Hope this helps, Thanks, David ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
