Hi so 2. 1. 2021 v 20:07 odesílatel Joel Jacobson <j...@compiler.org> napsal:
> I found yet another trick, which actually seems to be slightly faster than > the plpgsql version. > What version of Postgres do you use? plpgsql in Postgres 13 is significantly faster than on older versions Regards Pavel > The trick is to use VIEW ... WITH (security_barrier) to tell the optimizer > it shouldn’t flatten the subqueries. > > CREATE TABLE eastern (year integer); > > INSERT INTO eastern (year) SELECT generate_series(1,100000); > > CREATE VIEW v0 WITH (security_barrier) AS ( > SELECT > year, > year % 19 AS g, > year / 100 AS c > FROM eastern > ); > CREATE VIEW v1 WITH (security_barrier) AS ( > SELECT > year, > g, > c, > (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h > FROM v0 > ); > CREATE VIEW v2 WITH (security_barrier) AS ( > SELECT > year, > c, > h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i > FROM v1 > ); > CREATE VIEW v3 WITH (security_barrier) AS ( > SELECT > year, > i, > (year + year/4 + i + 2 - c + c/4) % 7 AS j > FROM v2 > ); > CREATE VIEW v4 WITH (security_barrier) AS ( > SELECT > year, > i - j AS p > FROM v3 > ); > CREATE VIEW v5 WITH (security_barrier) AS ( > SELECT > year, > 3 + (p + 26)/30 AS easter_month, > 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day > FROM v4 > ); > > EXPLAIN ANALYZE VERBOSE > SELECT make_date(year, easter_month, easter_day) FROM v5; > > > QUERY PLAN > > ----------------------------------------------------------------------------------------------------------------------------------------------------------- > Subquery Scan on v5 (cost=0.00..17943.00 rows=100000 width=4) (actual > time=0.025..213.996 rows=100000 loops=1) > Output: make_date(v5.year, v5.easter_month, v5.easter_day) > -> Subquery Scan on v4 (cost=0.00..16693.00 rows=100000 width=12) > (actual time=0.024..191.448 rows=100000 loops=1) > Output: v4.year, (3 + ((v4.p + 26) / 30)), (1 + (((v4.p + 27) + > ((v4.p + 6) / 40)) % 31)) > -> Subquery Scan on v3 (cost=0.00..13443.00 rows=100000 > width=8) (actual time=0.022..155.690 rows=100000 loops=1) > Output: v3.year, (v3.i - v3.j) > -> Subquery Scan on v2 (cost=0.00..12193.00 rows=100000 > width=12) (actual time=0.021..135.485 rows=100000 loops=1) > Output: v2.year, v2.i, ((((((v2.year + (v2.year / 4)) > + v2.i) + 2) - v2.c) + (v2.c / 4)) % 7) > -> Subquery Scan on v1 (cost=0.00..9193.00 > rows=100000 width=12) (actual time=0.019..97.935 rows=100000 loops=1) > Output: v1.year, v1.c, (v1.h - ((v1.h / 28) * > (1 - (((v1.h / 28) * (29 / (v1.h + 1))) * ((21 - v1.g) / 11))))) > -> Subquery Scan on v0 (cost=0.00..5443.00 > rows=100000 width=16) (actual time=0.017..57.988 rows=100000 loops=1) > Output: v0.year, v0.g, v0.c, (((((v0.c - > (v0.c / 4)) - (((8 * v0.c) + 13) / 25)) + (19 * v0.g)) + 15) % 30) > -> Seq Scan on public.eastern > (cost=0.00..1943.00 rows=100000 width=12) (actual time=0.015..23.908 > rows=100000 loops=1) > Output: eastern.year, (eastern.year > % 19), (eastern.year / 100) > Planning Time: 0.274 ms > Execution Time: 220.698 ms > (16 rows) > > EXPLAIN ANALYZE VERBOSE > SELECT easter_plpgsql(year) FROM eastern; > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------ > Seq Scan on public.eastern (cost=0.00..26443.00 rows=100000 width=4) > (actual time=0.077..301.519 rows=100000 loops=1) > Output: easter_plpgsql(year) > Planning Time: 0.049 ms > Execution Time: 309.119 ms > (4 rows) > > > On Tue, Dec 22, 2020, at 17:32, Tom Lane wrote: > > "Joel Jacobson" <j...@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 > >
