Loading ...
Sorry, an error occurred while loading the content.
 

Re: Trying to use "prepared statements" in PostgreSQL queries

Expand Messages
  • Jeroen Geilman
    ... You re obviously free to do that - but as Victor said, postfix doesn t support preparing statements, so you d have to hack the driver :) ... Even the
    Message 1 of 6 , Nov 1, 2010
      On 11/01/2010 08:40 PM, Patrick Ben Koetter wrote:
      > Jeroen,
      >
      > thanks for the detailed answer. Please read my annotations below.
      >
      > * Jeroen Geilman<jeroen@...>:
      >
      >> On 11/01/2010 07:35 PM, Patrick Ben Koetter wrote:
      >>
      >>> Out of curiosity I started to play around with Postfix and PostgreSQL.
      >>> PostgreSQL recommends "prepared statements" to speed up queries (by ~%20).
      >>>
      >> From the 8.0 manual:
      >>
      >> Prepared statements have the largest performance advantage when a
      >> single session is being used to execute a large number of similar
      >> statements. The performance difference will be particularly
      >> significant if the statements are complex to plan or rewrite, for
      >> example, if the query involves a join of many tables or requires the
      >> application of several rules. *If the statement is relatively simple
      >> to plan and rewrite but relatively expensive to execute, the
      >> performance advantage of prepared statements will be less
      >> noticeable.*
      >>
      >> It is doubtful whether a simple key lookup query - such as postfix
      >> does - benefits from PSs.
      >>
      > Agreed. I doubt that too, but I don't know a better approach to prove that
      > except for trying and measuring.
      >
      >
      >

      You're obviously free to do that - but as Victor said, postfix doesn't
      support preparing statements, so you'd have to hack the driver :)

      >> If the postgres database in question is used primarily to lookup
      >> postfix maps, every possible value will be cached in RAM for 99% of
      >> the time anyway - this gives incomparably larger advantages than
      >> writing faster queries.
      >>
      > So the best approach is to ensure all tables can be loaded into memory i.e.
      > provide enough $work_mem in pgSQL?
      >
      >

      Even the indexes would be enough. It depends on how big your dataset is.

      >>> As I understand it "prepared statements" must be defined once when a DB
      >>> session starts and they will be available only to the particular client that
      >>> requested the "prepared statement". Any subsequent client connecting will have
      >>> to PREPARE a "prepared statement" for itself.
      >>>
      >> A prepared statement remains in memory during a session, yes.
      >>
      >>
      >>> I see I can get around multiple PREPARE statements if I use the Postfix
      >>> proxymap daemon, but how would I send the initial PREPARE query?
      >>>
      >> That's untrivial, since even a proxymap connection doesn't live forever.
      >> All postfix processes are recycled after a period of time.
      >>
      >> If the Pl/pgSQL language allows it, you could write a SP that checks
      >> if the statement is already prepared, and then execute it.
      >> This will have a lot more overhead than the potential gain from
      >> preparing it.
      >>
      > Do I understand you correctly? Are you saying the potential gain is not worth
      > the effort?
      >

      I am saying exactly what I am saying ;)

      Given that A. postfix does not support preparing the select_query, and
      B. indexing properly will provide much bigger gains than any other
      measure (orders of magnitude bigger gains), and C. the manual suggests
      that using prepared statements is much less beneficial for simple
      queries, odds are that it's not going to be worth the effort.

      >> You should have absolutely no delusions about the performance cost
      >> of this extra check - just writing a stored procedure that runs the
      >> SELECT will win every single time.
      >>
      >>

      Thus.


      --
      J.
    • Kenneth Marshall
      It might be worth checking out the pre_prepare module: http://preprepare.projects.postgresql.org/README.html Cheers, Ken
      Message 2 of 6 , Nov 1, 2010
        It might be worth checking out the pre_prepare module:

        http://preprepare.projects.postgresql.org/README.html

        Cheers,
        Ken

        On Mon, Nov 01, 2010 at 08:45:17PM +0100, Jeroen Geilman wrote:
        > On 11/01/2010 08:40 PM, Patrick Ben Koetter wrote:
        >> Jeroen,
        >>
        >> thanks for the detailed answer. Please read my annotations below.
        >>
        >> * Jeroen Geilman<jeroen@...>:
        >>
        >>> On 11/01/2010 07:35 PM, Patrick Ben Koetter wrote:
        >>>
        >>>> Out of curiosity I started to play around with Postfix and PostgreSQL.
        >>>> PostgreSQL recommends "prepared statements" to speed up queries (by
        >>>> ~%20).
        >>>>
        >>> From the 8.0 manual:
        >>>
        >>> Prepared statements have the largest performance advantage when a
        >>> single session is being used to execute a large number of similar
        >>> statements. The performance difference will be particularly
        >>> significant if the statements are complex to plan or rewrite, for
        >>> example, if the query involves a join of many tables or requires the
        >>> application of several rules. *If the statement is relatively simple
        >>> to plan and rewrite but relatively expensive to execute, the
        >>> performance advantage of prepared statements will be less
        >>> noticeable.*
        >>>
        >>> It is doubtful whether a simple key lookup query - such as postfix
        >>> does - benefits from PSs.
        >>>
        >> Agreed. I doubt that too, but I don't know a better approach to prove that
        >> except for trying and measuring.
        >>
        >>
        >>
        >
        > You're obviously free to do that - but as Victor said, postfix doesn't
        > support preparing statements, so you'd have to hack the driver :)
        >
        >>> If the postgres database in question is used primarily to lookup
        >>> postfix maps, every possible value will be cached in RAM for 99% of
        >>> the time anyway - this gives incomparably larger advantages than
        >>> writing faster queries.
        >>>
        >> So the best approach is to ensure all tables can be loaded into memory
        >> i.e.
        >> provide enough $work_mem in pgSQL?
        >>
        >>
        >
        > Even the indexes would be enough. It depends on how big your dataset is.
        >
        >>>> As I understand it "prepared statements" must be defined once when a DB
        >>>> session starts and they will be available only to the particular client
        >>>> that
        >>>> requested the "prepared statement". Any subsequent client connecting
        >>>> will have
        >>>> to PREPARE a "prepared statement" for itself.
        >>>>
        >>> A prepared statement remains in memory during a session, yes.
        >>>
        >>>
        >>>> I see I can get around multiple PREPARE statements if I use the Postfix
        >>>> proxymap daemon, but how would I send the initial PREPARE query?
        >>>>
        >>> That's untrivial, since even a proxymap connection doesn't live forever.
        >>> All postfix processes are recycled after a period of time.
        >>>
        >>> If the Pl/pgSQL language allows it, you could write a SP that checks
        >>> if the statement is already prepared, and then execute it.
        >>> This will have a lot more overhead than the potential gain from
        >>> preparing it.
        >>>
        >> Do I understand you correctly? Are you saying the potential gain is not
        >> worth
        >> the effort?
        >>
        >
        > I am saying exactly what I am saying ;)
        >
        > Given that A. postfix does not support preparing the select_query, and B.
        > indexing properly will provide much bigger gains than any other measure
        > (orders of magnitude bigger gains), and C. the manual suggests that using
        > prepared statements is much less beneficial for simple queries, odds are
        > that it's not going to be worth the effort.
        >
        >>> You should have absolutely no delusions about the performance cost
        >>> of this extra check - just writing a stored procedure that runs the
        >>> SELECT will win every single time.
        >>>
        >>>
        >
        > Thus.
        >
        >
        > --
        > J.
        >
        >
      Your message has been successfully submitted and would be delivered to recipients shortly.