Обсуждение: CREATE FUNCTION ... performance boost?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hello all,

   In order to speed up commonly used queries, I use prepared
statements. I assume that 'prepare' tells some database query planner to
take a look at a query, and do all preparations for it, then store those
preparations somewhere for when it's needed.

   When I use CREATE FUNCTION to create a function, does that also get
"planned" automatically (plsql, specifically). My gut feeling is that it
doesn't (mainly since it can be any external language).

   The reason I'm asking is because I'd like to have permanently stored
"prepared" statements in a database. On even intervals, I connect to it,
and perform a number of queries. Many of them are the same statements
being run over and over. And these prepared statements really are "hard
coded", so they would do fine in my database initialization script.

   I assume that "functions" aren't what I am looking for.. But is is
possible to store preparations some way so they survive past
connection/disconnections?


- --
Kind regards,
Jan Danielsson

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (NetBSD)

iD8DBQFGhSxLuPlHKFfKXTYRCjb0AJ4utz24vsbwNkU0dGMpPyqg/c7azACfW+b7
0Ecy+yD3ayrvR+C+B8G9shQ=
=NFfn
-----END PGP SIGNATURE-----

On 6/29/07, Jan Danielsson <jan.m.danielsson@gmail.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Hello all,
>
>    In order to speed up commonly used queries, I use prepared
> statements. I assume that 'prepare' tells some database query planner to
> take a look at a query, and do all preparations for it, then store those
> preparations somewhere for when it's needed.
>
>    When I use CREATE FUNCTION to create a function, does that also get
> "planned" automatically (plsql, specifically). My gut feeling is that it
> doesn't (mainly since it can be any external language).

'execute' is pretty much the fastest possible way to execute a query
(or, even better, PQexecPrepared via C)...it's less cpu cycles than a
function although barely.       I would suggest maybe rethinking your
query into a view and querying that with or without prepare.

merlin

On 6/29/07, Jan Danielsson <jan.m.danielsson@gmail.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Hello all,
>
>    In order to speed up commonly used queries, I use prepared
> statements. I assume that 'prepare' tells some database query planner to
> take a look at a query, and do all preparations for it, then store those
> preparations somewhere for when it's needed.
>
>    When I use CREATE FUNCTION to create a function, does that also get
> "planned" automatically (plsql, specifically). My gut feeling is that it
> doesn't (mainly since it can be any external language).


by the way, query plans are exposed through spi interface so all pl
languages have ability to store plans.  pl/sql saves plans as well as
static sql in pl/pgsql.  ymmv on various other pls.

merlin

Jan Danielsson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA512
>
> Hello all,
>
>    In order to speed up commonly used queries, I use prepared
> statements. I assume that 'prepare' tells some database query planner to
> take a look at a query, and do all preparations for it, then store those
> preparations somewhere for when it's needed.

You might well be slowing things down. PREPARE/EXECUTE can't replan as
you change the parameters to the query. Now, in many cases that won't
matter, e.g. "SELECT * FROM foo WHERE x=? AND y=?" where there's a
unique index on x or y. What you are gaining is the cost of planning on
each call.

>    When I use CREATE FUNCTION to create a function, does that also get
> "planned" automatically (plsql, specifically). My gut feeling is that it
> doesn't (mainly since it can be any external language).

Well, plpgsql will have its plans "compiled" on the first call. That
will last as long as the connection.

>    The reason I'm asking is because I'd like to have permanently stored
> "prepared" statements in a database. On even intervals, I connect to it,
> and perform a number of queries. Many of them are the same statements
> being run over and over. And these prepared statements really are "hard
> coded", so they would do fine in my database initialization script.
>
>    I assume that "functions" aren't what I am looking for.. But is is
> possible to store preparations some way so they survive past
> connection/disconnections?

Nope. Plans last only until the end of a session.

What you might be able to do is use a connection pool/proxy to maintain
connections to the db while you connect to the proxy. That way the
connection can be made to last longer.

Before you go to too much effort though, I'd check that you're really
going to gain a useful performance boost.

--
   Richard Huxton
   Archonet Ltd