From: Richard Huxton <dev@archonet.com>
To: Jason Lustig <lustig@brandeis.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about SQL performance
Date: Tue, 05 Jun 2007 09:50:56 +0100
Message-ID: <466523F0.5020306@archonet.com> (raw)
In-Reply-To: <F84FCD04-CB11-4CA2-838A-0ABA75A7F1A2@brandeis.edu>
References: <F84FCD04-CB11-4CA2-838A-0ABA75A7F1A2@brandeis.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, lustig@brandeis.edu
Subject: Re: Question about SQL performance
In-Reply-To: <466523F0.5020306@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox