Thanks Tom,
this was exactly what I needed to hear.
I guess I recently have become too fond of all the nice new "recent" advanced SQL features,
such as LATERAL and MATERIALIZED CTEs, now in my possession since I now only code on hobby
projects, after all the years stuck in an old PostgreSQL version in my previous job,
to realise that such SQL features are not always a good fit for the job at all times.
I rewrote all the slow pure SQL code in PL/pgSQL and got as 568% speed-up in the CBOR to JSON converter I'm working on. Thanks also for giving me inspiration on the wording for my own commit message:
Author: Joel Jakobsson <joel@xxxxxxxxxxxx>
Date: Tue Dec 22 18:21:47 2020 +0100
Optimize by rewriting declarative pure SQL code into imperative PL/pgSQL
PostgreSQL's SQL language isn't terribly well suited to execute
a fundamentally stepwise, imperative algorithm like CBOR.
Rather than hacking up cute tricks with LATERAL, we should just use
a language that *is* well suited, a PL, like PL/pgSQL.
-- Pure SQL (before):
select * from pg_stat_xact_user_functions ;
schemaname | funcname | calls | total_time | self_time
------------+------------+-------+------------+-----------
cbor | next_item | 14 | 48.91024 | 38.964918
cbor | next_array | 1 | 7.297435 | 1.816102
cbor | next_map | 2 | 40.844352 | 7.8957
cbor | to_jsonb | 1 | 50.222183 | 1.311943
-- PL/pgSQL (after):
select * from pg_stat_xact_user_functions ;
schemaname | funcname | calls | total_time | self_time
------------+--------------+-------+------------+-----------
cbor | next_item | 14 | 8.021371 | 3.358271
cbor | next_array | 1 | 0.565398 | 0.353071
cbor | next_map | 2 | 5.607702 | 1.324057
cbor | to_jsonb | 1 | 8.823691 | 0.80232
FUNCTIONS/major_type_0.sql | 23 +++++++++++++++++++++++
FUNCTIONS/major_type_1.sql | 23 +++++++++++++++++++++++
FUNCTIONS/major_type_2.sql | 23 +++++++++++++++++++++++
FUNCTIONS/major_type_3.sql | 23 +++++++++++++++++++++++
FUNCTIONS/major_type_4.sql | 23 +++++++++++++++++++++++
FUNCTIONS/major_type_5.sql | 23 +++++++++++++++++++++++
FUNCTIONS/major_type_6.sql | 40 ++++++++++++++++++++++++++++++++++++++++
FUNCTIONS/major_type_7.sql | 43 +++++++++++++++++++++++++++++++++++++++++++
FUNCTIONS/next_item.sql | 109 ++++++++++++++++++++++++++++++++++++-------------------------------------------------------------------------
Makefile | 8 ++++++++
expected/rfc7049_appendix_a.out | 52 +++++++++++++++++++++++++++++++++++++++-------------
11 files changed, 304 insertions(+), 86 deletions(-)
Best regards,
Joel
On Tue, Dec 22, 2020, at 17:32, Tom Lane wrote:
"Joel Jacobson" <joel@xxxxxxxxxxxx> 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
Kind regards,
Joel
