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

On 09/05/13 02:47, Merlin Moncure wrote:
> On Wed, May 8, 2013 at 9:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Seref Arikan <serefarikan(at)kurumsalteknoloji(dot)com> 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