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