Search Postgresql Archives

Re: Avoid excessive inlining?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:

https://github.com/truthly/pg-cbor/commit/7ea7640f699cdf271ffa9cfb5d059f8141f541ed

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux