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?
PG will plan "raw" sql every time you issue a query.
SELECT * FROM mytable WHERE item = 5;
SELECT * FROM mytable WHERE item = 10;
Obviously to me or you they could use the same plan.
Except that in-between query 1 and 2 I inserted 10 million rows where
item=10. Still obvious?
> 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.
If you are using PREPARE/EXECUTE (or your client-side library is doing
it for you).
Also, what's the difference between prepared statements (using PREPARE
and EXECUTE) and regular functions (CREATE FUNCTION)? How do they impact
performance?
Functions can be in any language, but if they both are in SQL and do the
same thing, no real difference.
> From what I understand there is no exact parallel to stored
procedures (as in MS SQL or oracle, that are completely precompiled) in
Postgresql.
You can write functions in C - that's compiled. Not sure if java
procedural code has its byte-code cached between sessions.
> 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 don't see the connection.
1. You can write procedural code in pl/pgsql
2. It's not precompiled (it's "compiled" on first use)
Are you looking to solve a particular problem?
--
Richard Huxton
Archonet Ltd
