Question about SQL performance - Mailing list pgsql-performance

From Jason Lustig
Subject Question about SQL performance
Date
Msg-id F84FCD04-CB11-4CA2-838A-0ABA75A7F1A2@brandeis.edu
Whole thread Raw
Responses Re: Question about SQL performance  (PFC <lists@peufeu.com>)
Re: Question about SQL performance  (mark@mark.mielke.cc)
Re: Question about SQL performance  (Richard Huxton <dev@archonet.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Dave Cramer
Date:
Subject: postgresql running on a virtual cluster
Next
From: PFC
Date:
Subject: Re: Question about SQL performance