Re: JIT doing duplicative optimization?

On Sun, Nov 14, 2021 at 9:07 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:

> On 2021-Nov-11, Alvaro Herrera wrote:
>
> > But what really surprised me is that the the average time to optimize
> > per function is now 2.06ms ... less than half of the previous
> > measurement. It emits 10% less functions than before, but the time to
> > both optimize and emit is reduced by 50%. How does that make sense?
>
> Ah, here's a query that illustrates what I'm on about. I found this
> query[1] in a blog post[2].
>
> ```
> WITH RECURSIVE typeinfo_tree(
> oid, ns, name, kind, basetype, elemtype, elemdelim,
> range_subtype, attrtypoids, attrnames, depth)
> AS (
> SELECT
> ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
> ti.elemtype, ti.elemdelim, ti.range_subtype,
> ti.attrtypoids, ti.attrnames, 0
> FROM
> (
> SELECT
> t.oid AS oid,
> ns.nspname AS ns,
> t.typname AS name,
> t.typtype AS kind,
> (CASE WHEN t.typtype = 'd' THEN
> (WITH RECURSIVE typebases(oid, depth) AS (
> SELECT
> t2.typbasetype AS oid,
> 0 AS depth
> FROM
> pg_type t2
> WHERE
> t2.oid = t.oid
> UNION ALL
> SELECT
> t2.typbasetype AS oid,
> tb.depth + 1 AS depth
> FROM
> pg_type t2,
> typebases tb
> WHERE
> tb.oid = t2.oid
> AND t2.typbasetype != 0
> ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
> ELSE NULL
> END) AS basetype,
> t.typelem AS elemtype,
> elem_t.typdelim AS elemdelim,
> range_t.rngsubtype AS range_subtype,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.atttypid ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrtypoids,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.attname::text ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrnames
> FROM
> pg_catalog.pg_type AS t
> INNER JOIN pg_catalog.pg_namespace ns ON (
> ns.oid = t.typnamespace)
> LEFT JOIN pg_type elem_t ON (
> t.typlen = -1 AND
> t.typelem != 0 AND
> t.typelem = elem_t.oid
> )
> LEFT JOIN pg_range range_t ON (
> t.oid = range_t.rngtypid
> )
> ) AS ti
> WHERE
> ti.oid = any(ARRAY[16,17]::oid[])
>
> UNION ALL
>
> SELECT
> ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
> ti.elemtype, ti.elemdelim, ti.range_subtype,
> ti.attrtypoids, ti.attrnames, tt.depth + 1
> FROM
> (
> SELECT
> t.oid AS oid,
> ns.nspname AS ns,
> t.typname AS name,
> t.typtype AS kind,
> (CASE WHEN t.typtype = 'd' THEN
> (WITH RECURSIVE typebases(oid, depth) AS (
> SELECT
> t2.typbasetype AS oid,
> 0 AS depth
> FROM
> pg_type t2
> WHERE
> t2.oid = t.oid
> UNION ALL
> SELECT
> t2.typbasetype AS oid,
> tb.depth + 1 AS depth
> FROM
> pg_type t2,
> typebases tb
> WHERE
> tb.oid = t2.oid
> AND t2.typbasetype != 0
> ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
> ELSE NULL
> END) AS basetype,
> t.typelem AS elemtype,
> elem_t.typdelim AS elemdelim,
> range_t.rngsubtype AS range_subtype,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.atttypid ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrtypoids,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.attname::text ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrnames
> FROM
> pg_catalog.pg_type AS t
> INNER JOIN pg_catalog.pg_namespace ns ON (
> ns.oid = t.typnamespace)
> LEFT JOIN pg_type elem_t ON (
> t.typlen = -1 AND
> t.typelem != 0 AND
> t.typelem = elem_t.oid
> )
> LEFT JOIN pg_range range_t ON (
> t.oid = range_t.rngtypid
> )
> ) ti,
> typeinfo_tree tt
> WHERE
> (tt.elemtype IS NOT NULL AND ti.oid = tt.elemtype)
> OR (tt.attrtypoids IS NOT NULL AND ti.oid = any(tt.attrtypoids))
> OR (tt.range_subtype IS NOT NULL AND ti.oid = tt.range_subtype)
> )
> SELECT DISTINCT
> *,
> basetype::regtype::text AS basetype_name,
> elemtype::regtype::text AS elemtype_name,
> range_subtype::regtype::text AS range_subtype_name
> FROM
> typeinfo_tree
> ORDER BY
> depth DESC;
> ```
>
> I did an EXPLAIN ANALYZE and at the bottom you see this:
>
> Planning Time: 2.606 ms
> JIT:
> Functions: 148
> Options: Inlining true, Optimization true, Expressions true, Deforming
> true
> Timing: Generation 19.670 ms, Inlining 19.224 ms, Optimization 435.153
> ms, Emission 282.216 ms, Total 756.263 ms
> Execution Time: 757.643 ms
>
> Average time to optimize, per function 435.153/148 = 2.940ms;
> average time to emit per function 282.216/148 = 1.906ms
>
> Now let's change the query by making the first innermost recursive CTE into
> non-recursive; just delete the RECURSIVE keyword and everything after the
> UNION
> ALL. You now get this query.
>
> ```
> explain analyze
> WITH RECURSIVE typeinfo_tree(
> oid, ns, name, kind, basetype, elemtype, elemdelim,
> range_subtype, attrtypoids, attrnames, depth)
> AS (
> SELECT
> ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
> ti.elemtype, ti.elemdelim, ti.range_subtype,
> ti.attrtypoids, ti.attrnames, 0
> FROM
> (
> SELECT
> t.oid AS oid,
> ns.nspname AS ns,
> t.typname AS name,
> t.typtype AS kind,
> (CASE WHEN t.typtype = 'd' THEN
> (WITH typebases(oid, depth) AS (
> SELECT
> t2.typbasetype AS oid,
> 0 AS depth
> FROM
> pg_type t2
> WHERE
> t2.oid = t.oid
> ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
> ELSE NULL
> END) AS basetype,
> t.typelem AS elemtype,
> elem_t.typdelim AS elemdelim,
> range_t.rngsubtype AS range_subtype,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.atttypid ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrtypoids,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.attname::text ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrnames
> FROM
> pg_catalog.pg_type AS t
> INNER JOIN pg_catalog.pg_namespace ns ON (
> ns.oid = t.typnamespace)
> LEFT JOIN pg_type elem_t ON (
> t.typlen = -1 AND
> t.typelem != 0 AND
> t.typelem = elem_t.oid
> )
> LEFT JOIN pg_range range_t ON (
> t.oid = range_t.rngtypid
> )
> ) AS ti
> WHERE
> ti.oid = any(ARRAY[16,17]::oid[])
>
> UNION ALL
>
> SELECT
> ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
> ti.elemtype, ti.elemdelim, ti.range_subtype,
> ti.attrtypoids, ti.attrnames, tt.depth + 1
> FROM
> (
> SELECT
> t.oid AS oid,
> ns.nspname AS ns,
> t.typname AS name,
> t.typtype AS kind,
> (CASE WHEN t.typtype = 'd' THEN
> (WITH RECURSIVE typebases(oid, depth) AS (
> SELECT
> t2.typbasetype AS oid,
> 0 AS depth
> FROM
> pg_type t2
> WHERE
> t2.oid = t.oid
> UNION ALL
> SELECT
> t2.typbasetype AS oid,
> tb.depth + 1 AS depth
> FROM
> pg_type t2,
> typebases tb
> WHERE
> tb.oid = t2.oid
> AND t2.typbasetype != 0
> ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
> ELSE NULL
> END) AS basetype,
> t.typelem AS elemtype,
> elem_t.typdelim AS elemdelim,
> range_t.rngsubtype AS range_subtype,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.atttypid ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrtypoids,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.attname::text ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrnames
> FROM
> pg_catalog.pg_type AS t
> INNER JOIN pg_catalog.pg_namespace ns ON (
> ns.oid = t.typnamespace)
> LEFT JOIN pg_type elem_t ON (
> t.typlen = -1 AND
> t.typelem != 0 AND
> t.typelem = elem_t.oid
> )
> LEFT JOIN pg_range range_t ON (
> t.oid = range_t.rngtypid
> )
> ) ti,
> typeinfo_tree tt
> WHERE
> (tt.elemtype IS NOT NULL AND ti.oid = tt.elemtype)
> OR (tt.attrtypoids IS NOT NULL AND ti.oid = any(tt.attrtypoids))
> OR (tt.range_subtype IS NOT NULL AND ti.oid = tt.range_subtype)
> )
>
> SELECT DISTINCT
> *,
> basetype::regtype::text AS basetype_name,
> elemtype::regtype::text AS elemtype_name,
> range_subtype::regtype::text AS range_subtype_name
> FROM
> typeinfo_tree
> ORDER BY
> depth DESC
> ```
>
> At the bottom of the explain you get this.
>
> Planning Time: 2.508 ms
> JIT:
> Functions: 137
> Options: Inlining true, Optimization true, Expressions true, Deforming
> true
> Timing: Generation 10.346 ms, Inlining 10.210 ms, Optimization 374.103
> ms, Emission 254.557 ms, Total 649.216 ms
> Execution Time: 650.168 ms
>
> Average time to optimize, per function: 374.103/137 = 2.730ms
> Average time to emit, per function 254.557 / 137 = 1.858ms
>
>
> Now do it one more time, with the second innermost recursive CTE. You get
> this
> query.
>
> ```
> explain analyze
> WITH RECURSIVE typeinfo_tree(
> oid, ns, name, kind, basetype, elemtype, elemdelim,
> range_subtype, attrtypoids, attrnames, depth)
> AS (
> SELECT
> ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
> ti.elemtype, ti.elemdelim, ti.range_subtype,
> ti.attrtypoids, ti.attrnames, 0
> FROM
> (
> SELECT
> t.oid AS oid,
> ns.nspname AS ns,
> t.typname AS name,
> t.typtype AS kind,
> (CASE WHEN t.typtype = 'd' THEN
> (WITH typebases(oid, depth) AS (
> SELECT
> t2.typbasetype AS oid,
> 0 AS depth
> FROM
> pg_type t2
> WHERE
> t2.oid = t.oid
> ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
> ELSE NULL
> END) AS basetype,
> t.typelem AS elemtype,
> elem_t.typdelim AS elemdelim,
> range_t.rngsubtype AS range_subtype,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.atttypid ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrtypoids,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.attname::text ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrnames
> FROM
> pg_catalog.pg_type AS t
> INNER JOIN pg_catalog.pg_namespace ns ON (
> ns.oid = t.typnamespace)
> LEFT JOIN pg_type elem_t ON (
> t.typlen = -1 AND
> t.typelem != 0 AND
> t.typelem = elem_t.oid
> )
> LEFT JOIN pg_range range_t ON (
> t.oid = range_t.rngtypid
> )
> ) AS ti
> WHERE
> ti.oid = any(ARRAY[16,17]::oid[])
>
> UNION ALL
>
> SELECT
> ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
> ti.elemtype, ti.elemdelim, ti.range_subtype,
> ti.attrtypoids, ti.attrnames, tt.depth + 1
> FROM
> (
> SELECT
> t.oid AS oid,
> ns.nspname AS ns,
> t.typname AS name,
> t.typtype AS kind,
> (CASE WHEN t.typtype = 'd' THEN
> (WITH typebases(oid, depth) AS (
> SELECT
> t2.typbasetype AS oid,
> 0 AS depth
> FROM
> pg_type t2
> WHERE
> t2.oid = t.oid
> ) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
> ELSE NULL
> END) AS basetype,
> t.typelem AS elemtype,
> elem_t.typdelim AS elemdelim,
> range_t.rngsubtype AS range_subtype,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.atttypid ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrtypoids,
> (CASE WHEN t.typtype = 'c' THEN
> (SELECT
> array_agg(ia.attname::text ORDER BY ia.attnum)
> FROM
> pg_attribute ia
> INNER JOIN pg_class c
> ON (ia.attrelid = c.oid)
> WHERE
> ia.attnum > 0 AND NOT ia.attisdropped
> AND c.reltype = t.oid)
> ELSE NULL
> END) AS attrnames
> FROM
> pg_catalog.pg_type AS t
> INNER JOIN pg_catalog.pg_namespace ns ON (
> ns.oid = t.typnamespace)
> LEFT JOIN pg_type elem_t ON (
> t.typlen = -1 AND
> t.typelem != 0 AND
> t.typelem = elem_t.oid
> )
> LEFT JOIN pg_range range_t ON (
> t.oid = range_t.rngtypid
> )
> ) ti,
> typeinfo_tree tt
> WHERE
> (tt.elemtype IS NOT NULL AND ti.oid = tt.elemtype)
> OR (tt.attrtypoids IS NOT NULL AND ti.oid = any(tt.attrtypoids))
> OR (tt.range_subtype IS NOT NULL AND ti.oid = tt.range_subtype)
> )
>
> SELECT DISTINCT
> *,
> basetype::regtype::text AS basetype_name,
> elemtype::regtype::text AS elemtype_name,
> range_subtype::regtype::text AS range_subtype_name
> FROM
> typeinfo_tree
> ORDER BY
> depth DESC;
> ```
>
> And the JIT numbers at the bottom look like this:
>
> Planning Time: 2.041 ms
> JIT:
> Functions: 126
> Options: Inlining false, Optimization true, Expressions true, Deforming
> true
> Timing: Generation 10.002 ms, Inlining 0.000 ms, Optimization 229.128
> ms, Emission 167.338 ms, Total 406.469 ms
> Execution Time: 407.315 ms
>
> Average time to optimize per function 229.128 / 126 = 1.181ms
> Average time to emit per function 167.338 / 126 = 1.328ms
>
>
> In summary,
>
> Query 1, 148 functions JIT-compiled.
> Average time to optimize, per function 435.153/148 = 2.940ms;
> average time to emit per function 282.216/148 = 1.906ms
>
> Query 2, 137 functions JIT-compiled.
> Average time to optimize, per function: 374.103/137 = 2.730ms
> Average time to emit, per function 254.557 / 137 = 1.858ms
>
> Query 3, 126 functions JIT-compiled.
> Average time to optimize per function 229.128 / 126 = 1.181ms
> Average time to emit per function 167.338 / 126 = 1.328ms
>
> ???? Something looks very wrong here.
>
> [1] https://gist.github.com/saicitus/251ba20b211e9e73285af35e61b19580
> [2]
> https://dev.to/xenatisch/cascade-of-doom-jit-and-how-a-postgres-update-led-to-70-failure-on-a-critical-national-service-3f2a
>
> --
> Álvaro Herrera PostgreSQL Developer —
> https://www.EnterpriseDB.com/
> "Java is clearly an example of money oriented programming" (A. Stepanov)
>
>
> Hi,
For the 3rd query, I noticed some difference in options:

Options: Inlining false, Optimization true, Expressions true, Deforming
true

Inlining was true for the first two queries.

FYI