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

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

Expand Messages
  • Patrick Ben Koetter
    Jeroen, thanks for the detailed answer. Please read my annotations below. ... Agreed. I doubt that too, but I don t know a better approach to prove that except
    Message 1 of 6 , Nov 1, 2010
    • 0 Attachment
      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.


      > 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?


      > >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?

      p@rick


      > 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.
      >
      > --
      > J.
      >

      --
      All technical questions asked privately will be automatically answered on the
      list and archived for public access unless privacy is explicitely required and
      justified.

      saslfinger (debugging SMTP AUTH):
      <http://postfix.state-of-mind.de/patrick.koetter/saslfinger/>
    • 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 2 of 6 , Nov 1, 2010
      • 0 Attachment
        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 3 of 6 , Nov 1, 2010
        • 0 Attachment
          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.