"Joel Jacobson" <joel@compiler.org> writes:
> I think I was a bit unclear about my problem, and might have used the wrong terminology.
> In my LATERAL query, there are calculations in a certain order.
> For each step, "columns" are computed named e.g. "g", "c", "h", "i", etc.
> However, when looking at the query plan, these steps are gone, and instead there is just one huge fully expanded
expression,which doesn't look very efficient.
Yeah, this isn't really about function inlining, it's about subquery
flattening (which is similar in some ways, but not the same thing).
Unfortunately, subquery flattening happens early enough in the planner
that there's no chance of making any useful cost comparisons to decide
whether to do it or not. So we just do it unconditionally. I'm
not really sure that failing to do it would provide a better outcome
in this situation anyway --- sure, you'd save a few scalar calculations,
but the overhead of running additional plan nodes could outweigh that.
The long and the short of it is that SQL isn't terribly well suited to
execute a fundamentally stepwise, imperative algorithm like this one.
Rather than hacking up cute tricks with LATERAL, you should just use
a language that *is* well suited. That's why we provide PLs.
FWIW, another trick for inserting optimization fences is WITH.
So you could do something like
WITH Q1(g,c) AS MATERIALIZED
(SELECT year % 19, year / 100),
Q2(h) AS MATERIALIZED
(SELECT (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 FROM Q1),
...
SELECT make_date(year, easter_month, easter_day) FROM Q6;
But I'd bet lunch that that won't be faster for this example,
because there's a lot of overhead in CTEs.
regards, tom lane