Re: Does it make sense to break a large query into separate functions?

Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> · Thu, 09 May 2013 09:03:20 +1200

    On 09/05/13 02:47, Merlin Moncure
      wrote:

      On Wed, May 8, 2013 at 9:05 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

        Seref Arikan <serefarikan@xxxxxxxxxxxxxxxxxxxxx> writes:

          I've just noticed that some parts of the autogenerated queries can be
functions on their own. Would moving these parts to their own functions
help the query planner?

        It's difficult to answer that without a lot more detail than you've
provided, but my offhand guess would be "no".  Usually it's better
when the planner can expand functions inline, which would just be
reversing the transformation you're thinking of.

      In my experience virtually no useful functions are inlined by the
planner.   For example, with function:

create function f(text) returns date as $$
  select to_date($1, 'YYYY'); $$
language sql stable;  /* immutable doesn't help */

I see about 4x time difference between:
select to_date(v::text, 'YYYY') from generate_series(1,100000) v;
and
select f(v::text) from generate_series(1,100000) v;

I'm curious if more aggressive inlining is a future performance
optimization target for postgres or if there is some fundamental
restriction that prevents such functions from being inlined.  From an
abstraction point of view, I'd really like to be able to push more
code into functions, but often can't because of performance issues.

merlin

    +100

      I would very much like to split SQL queries into
        more manageable parts without loss of
          performance.  I have had SQL queries
          spanning over a page, and had a sequence of SQL queries
                with a lot in common.  So if I could move the common
                bits out, it would have made it more maintainable. 
                This was a few years ago in Sybase, but I would have had
                the same issues in Postgres.

                I remember having the some complicated SQL queries with multiple sub selects,
                      that had a lot of duplication within the same
                      query, which I would have like to have removed. Common table expressions (the WITH
                        clause) may have helped, but not that much.

                        However, common
                                              table expressions would
                                              have eliminated the need for some
                                                temporary tables, but
                                                made for some much
                                                longer SQL
                                                          queries.  This
                                                          was in a
                                                          stored
                                                          procedure that
                                                          was over
                                                          3,000
                                                          lines long -
                                                          in SyBase, but
                                                          I keep
                                                          thinking how I
                                                          would have
                                                          done it in Postgres
                                                          (I knew of Postgres,
                                                          but did not
                                                          have the
                                                          option to use
                                                          it).

                Cheers,

                  Gavin