Re: JIT doing duplicative optimization?
От | Alvaro Herrera |
---|---|
Тема | Re: JIT doing duplicative optimization? |
Дата | |
Msg-id | 202111141706.wqq7xoyigwa2@alvherre.pgsql обсуждение исходный текст |
Ответ на | JIT doing duplicative optimization? (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
Ответы |
Re: JIT doing duplicative optimization?
(Zhihong Yu <zyu@yugabyte.com>)
Re: JIT doing duplicative optimization? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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)
В списке pgsql-hackers по дате отправления: