On Mon, Jun 04, 2007 at 11:18:30PM -0400, Jason Lustig wrote: > I have some questions about the performance of certain types of SQL > statements. > > What sort of speed increase is there usually with binding parameters > (and thus preparing statements) v. straight sql with interpolated > variables? Will Postgresql realize that the following queries are > effectively the same (and thus re-use the query plan) or will it > think they are different? > > SELECT * FROM mytable WHERE item = 5; > SELECT * FROM mytable WHERE item = 10; > > Obviously to me or you they could use the same plan. From what I > understand (correct me if I'm wrong), if you use parameter binding - > like "SELECT * FROM mytable WHERE item = ?" - Postgresql will know > that the queries can re-use the query plan, but I don't know if the > system will recognize this with above situation. Although they could use the same plan, it is possible that using the same plan is non-optimal. For example, if I know that 99% of the table contains item = 5, but only 1% of the table contains item = 10, then the 'best plan' may be a sequential scan for item = 5, but an index scan for item = 10. In the case of a prepared query, PostgreSQL will pick a plan that will be good for all values, which may not be best for specific queries. You save parsing time and planning time, but may risk increasing execution time. > Also, what's the difference between prepared statements (using > PREPARE and EXECUTE) and regular functions (CREATE FUNCTION)? How do > they impact performance? From what I understand there is no exact > parallel to stored procedures (as in MS SQL or oracle, that are > completely precompiled) in Postgresql. At the same time, the > documentation (and other sites as well, probably because they don't > know what they're talking about when it comes to databases) is vague > because PL/pgSQL is often said to be able to write stored procedures > but nowhere does it say that PL/pgSQL programs are precompiled. I think you can find all of these answers in the documentation, including my comments about prepared queries. Does it matter if the program is precompiled? I believe it is, but why would it matter? Are you addressing a real performance problem? Or are you trying to avoid issues that you are not sure if they exist or not? :-) Prepared queries are going to improve performance due to being able to execute multiple queries without communicating back to the client. Especially for short queries, network latency can be a significant factor for execution speed. Cheers, mark -- mark@xxxxxxxxx / markm@xxxxxx / markm@xxxxxxxxxx __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/