Re: Avoid excessive inlining?

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:

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@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

Kind regards,

Joel