From: mark@mark.mielke.cc
To: Jason Lustig <lustig@brandeis.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about SQL performance
Date: Tue, 5 Jun 2007 01:23:12 -0400
Message-ID: <20070605052312.GA16345@mark.mielke.cc> (raw)
In-Reply-To: <F84FCD04-CB11-4CA2-838A-0ABA75A7F1A2@brandeis.edu>
References: <F84FCD04-CB11-4CA2-838A-0ABA75A7F1A2@brandeis.edu>
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@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | 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/
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: mark@mark.mielke.cc, lustig@brandeis.edu
Subject: Re: Question about SQL performance
In-Reply-To: <20070605052312.GA16345@mark.mielke.cc>
* 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