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.
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.
Thanks
Jason