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

Trying to use "prepared statements" in PostgreSQL queries

Expand Messages
  • Patrick Ben Koetter
    Out of curiosity I started to play around with Postfix and PostgreSQL. PostgreSQL recommends prepared statements to speed up queries (by ~%20). As I
    Message 1 of 6 , Nov 1, 2010
    • 0 Attachment
      Out of curiosity I started to play around with Postfix and PostgreSQL.
      PostgreSQL recommends "prepared statements" to speed up queries (by ~%20).

      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.

      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?

      Has anyone ever tried this? Is it "if its not documented, then its not there"?

      p@rick

      --
      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/>
    • Victor Duchovni
      ... You need to customize the Postfix PgSQL driver to (automatically) support prepared statements. -- Viktor.
      Message 2 of 6 , Nov 1, 2010
      • 0 Attachment
        On Mon, Nov 01, 2010 at 07:35:44PM +0100, 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).
        >
        > 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.
        >
        > 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?
        >
        > Has anyone ever tried this? Is it "if its not documented, then its not there"?

        You need to customize the Postfix PgSQL driver to (automatically) support
        prepared statements.

        --
        Viktor.
      • Jeroen Geilman
        ... 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
        Message 3 of 6 , Nov 1, 2010
        • 0 Attachment
          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.

          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.

          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.

          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.
          
        • 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 4 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 5 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 6 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.