6 ms·

If done right they can be huge. The fact that they do nothing when you just do "prepare" followed immediately by "execute" in place of just running the query d

If done right they can be huge. The fact that they do nothing when you just do "prepare" followed immediately by "execute" in place of just running the query directly is not a problem with postgresql or prepared statements, it is a problem of doing something dumb. For simple selects the parsing and planning stage can be 90% of the time spent. The problem is that postgresql doesn't have stored procedures, so you have to make sure every connection is initialized by running all of your "prepare"s before it is made available to the pool.

stefantalpalaru 13y ago
> The problem is that postgresql doesn't have stored procedures Of course it has: http://www.postgresql.org/docs/9.3/static/sql-createfunction.html http://www.postgresql.org/docs/9.3/static/sql-createfunction... And you can choose between 4 languages: http://www.postgresql.org/docs/9.3/interactive/xplang.html http://www.postgresql.org/docs/9.3/interactive/xplang.html
- nousernamesleft 13y ago
  Those are functions, not stored procedures. Every time that function is executed, it is re-parsed and re-planned.
  - dbenhur 13y ago
    The default stored procedure language is PL/pgSQL http://www.postgresql.org/docs/9.3/static/plpgsql.html http://www.postgresql.org/docs/9.3/static/plpgsql.html and has been available in Postgres almost forever.
    - nousernamesleft 13y ago
      Those are functions, not stored procedures.
  - chanks 13y ago
    PL/pgSQL function plans are cached. Functions written in other languages may not be, I don't know, but in my experience PL/pgSQL is the most widely used. http://www.postgresql.org/docs/9.3/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING http://www.postgresql.org/docs/9.3/static/plpgsql-implementa...
    - nousernamesleft 13y ago
      >PL/pgSQL function plans are cached Read your link. They can be cached. You have no way to ensure that they are. And you have no way to have the planning done up front at all, you have to wait until the function is run, and even then every code path in the function has to be exercised to get it planned. And then half the time it doesn't think it should bother to cache the plan anyways. And it is per-session, not shared. So every single connection has to start over with an empty cache every time, and there's tons of duplicate plans in the cache wasting space. Not cool, and by far the biggest thing keeping a ton of "enterprise" customers on SQL server and oracle.
      - chanks 13y ago
        You have some valid criticisms of the implementation, though pgsql-hackers has their reasons (http://www.postgresql.org/message-id/CAHyXU0ybwZZUbuQQVFQMK3wdcKMsd8GqqDteTnne_AXyHrF7fg@mail.gmail.com http://www.postgresql.org/message-id/CAHyXU0ybwZZUbuQQVFQMK3...) for the way things are. Regardless, my point is that the situation is not so bad as "Every time that function is executed, it is re-parsed and re-planned."
dbenhur 13y ago
You can get detailed timing data about parse, plan, execute timings from postgresql logging. Parsing is almost always trivial. Planning can get expensive for complex queries with many joins (large space of solutions to explore). For simple queries there's almost no benefit for prepared statements in postgres. Prepared statements are a maintenance headache and don't play well with session multiplexed connection pooling (like PgBouncer); generally best to avoid them unless you have measured a concrete and significant benefit.
- nousernamesleft 13y ago
  >You can get detailed timing data about parse, plan, execute timings from postgresql logging. Parsing is almost always trivial. And yet you can get >50% speedups for super common queries like "select * from foo where id = ?" if you prepare it once and then use execute instead of just running the select every time. Seems like maybe you're making assumptions you shouldn't.
justincormack 13y ago
I think the statements I benchmarked were more complex and slower, so there wasnt much in it. It was a while ago though. For simple selects it could make much more difference.

stefantalpalaru 13y ago

> The problem is that postgresql doesn't have stored procedures Of course it has: http://www.postgresql.org/docs/9.3/static/sql-createfunction.html http://www.postgresql.org/docs/9.3/static/sql-createfunction... And you can choose between 4 languages: http://www.postgresql.org/docs/9.3/interactive/xplang.html http://www.postgresql.org/docs/9.3/interactive/xplang.html

nousernamesleft 13y ago
Those are functions, not stored procedures. Every time that function is executed, it is re-parsed and re-planned.
- dbenhur 13y ago
  The default stored procedure language is PL/pgSQL http://www.postgresql.org/docs/9.3/static/plpgsql.html http://www.postgresql.org/docs/9.3/static/plpgsql.html and has been available in Postgres almost forever.
  - nousernamesleft 13y ago
    Those are functions, not stored procedures.
- chanks 13y ago
  PL/pgSQL function plans are cached. Functions written in other languages may not be, I don't know, but in my experience PL/pgSQL is the most widely used. http://www.postgresql.org/docs/9.3/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING http://www.postgresql.org/docs/9.3/static/plpgsql-implementa...
  - nousernamesleft 13y ago
    >PL/pgSQL function plans are cached Read your link. They can be cached. You have no way to ensure that they are. And you have no way to have the planning done up front at all, you have to wait until the function is run, and even then every code path in the function has to be exercised to get it planned. And then half the time it doesn't think it should bother to cache the plan anyways. And it is per-session, not shared. So every single connection has to start over with an empty cache every time, and there's tons of duplicate plans in the cache wasting space. Not cool, and by far the biggest thing keeping a ton of "enterprise" customers on SQL server and oracle.
    - chanks 13y ago
      You have some valid criticisms of the implementation, though pgsql-hackers has their reasons (http://www.postgresql.org/message-id/CAHyXU0ybwZZUbuQQVFQMK3wdcKMsd8GqqDteTnne_AXyHrF7fg@mail.gmail.com http://www.postgresql.org/message-id/CAHyXU0ybwZZUbuQQVFQMK3...) for the way things are. Regardless, my point is that the situation is not so bad as "Every time that function is executed, it is re-parsed and re-planned."

dbenhur 13y ago

You can get detailed timing data about parse, plan, execute timings from postgresql logging. Parsing is almost always trivial. Planning can get expensive for complex queries with many joins (large space of solutions to explore). For simple queries there's almost no benefit for prepared statements in postgres. Prepared statements are a maintenance headache and don't play well with session multiplexed connection pooling (like PgBouncer); generally best to avoid them unless you have measured a concrete and significant benefit.

nousernamesleft 13y ago
>You can get detailed timing data about parse, plan, execute timings from postgresql logging. Parsing is almost always trivial. And yet you can get >50% speedups for super common queries like "select * from foo where id = ?" if you prepare it once and then use execute instead of just running the select every time. Seems like maybe you're making assumptions you shouldn't.

justincormack 13y ago

I think the statements I benchmarked were more complex and slower, so there wasnt much in it. It was a while ago though. For simple selects it could make much more difference.