Обсуждение: problem with query
this query takes more than 8 seconds,
if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
FROM
(WITH partitions AS
(SELECT array
(WITH partitioned_tables AS
(SELECT array
(SELECT oid
FROM pg_class
WHERE relkind = 'p') AS parent_tables) SELECT child.relname AS PARTITION
FROM partitioned_tables, pg_inherits
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE
((nmsp_child.nspname='servicedesk'))
AND pg_inherits.inhparent = ANY (partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS table_schema,
pgc.relname AS TABLE_NAME,
CASE
WHEN pgc.relkind = 'r' THEN 'TABLE'
WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'
WHEN pgc.relkind = 'v' THEN 'VIEW'
WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'
WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'
END AS table_type,
obj_description(pgc.oid) AS COMMENT,
COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (
WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns,
COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB || jsonb_build_object('comment', obj_description(pgt.oid))) filter (
WHERE ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS,
row_to_json(isv) AS view_info
FROM partitions,
pg_class AS pgc
INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns */ /* This is a simplified version of how information_schema.columns was ** implemented in postgres 9.5, but modified to support materialized ** views. */
LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
a.attname AS COLUMN_NAME,
a.attnum AS ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
CASE
WHEN a.attnotnull
OR (t.typtype = 'd'
AND t.typnotnull) THEN 'NO'
ELSE 'YES'
END AS is_nullable,
CASE
WHEN t.typtype = 'd' THEN CASE
WHEN bt.typelem <> 0
AND bt.typlen = -1 THEN 'ARRAY'
WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, NULL)
ELSE 'USER-DEFINED'
END
ELSE CASE
WHEN t.typelem <> 0
AND t.typlen = -1 THEN 'ARRAY'
WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, NULL)
ELSE 'USER-DEFINED'
END
END AS data_type,
coalesce(bt.typname, t.typname) AS data_type_name,
CASE
WHEN a.attidentity = 'd' THEN TRUE
WHEN a.attidentity = 'a' THEN TRUE
ELSE FALSE
END AS is_identity
FROM (pg_attribute a
LEFT JOIN pg_attrdef ad ON attrelid = adrelid
AND attnum = adnum)
JOIN (pg_class c
JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
JOIN (pg_type t
JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd'
AND t.typbasetype = bt.oid)
LEFT JOIN (pg_collation co
JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON a.attcollation = co.oid
AND (nco.nspname,
co.collname) <> ('pg_catalog',
'default')
WHERE (NOT pg_is_other_temp_schema(nc.oid))
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relkind in ('r',
'v',
'm',
'f',
'p')
AND (pg_has_role(c.relowner, 'USAGE')
OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname
AND isc.table_name = pgc.relname
AND isc.column_name = pga.attname /* triggers */
LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid
LEFT OUTER JOIN information_schema.triggers AS ist ON ist.event_object_schema = pgn.nspname
AND ist.event_object_table = pgc.relname
AND ist.trigger_name = pgt.tgname /* This is a simplified version of how information_schema.views was ** implemented in postgres 9.5, but modified to support materialized ** views. */
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
CASE
WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid)
ELSE NULL
END AS view_definition,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN 'YES'
ELSE 'NO'
END AS is_updatable,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN 'YES'
ELSE 'NO'
END AS is_insertable_into,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 81 = 81) THEN 'YES'
ELSE 'NO'
END AS is_trigger_updatable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 73 = 73) THEN 'YES'
ELSE 'NO'
END AS is_trigger_deletable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 69 = 69) THEN 'YES'
ELSE 'NO'
END AS is_trigger_insertable_into
FROM pg_namespace nc,
pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind in ('v',
'm')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname
AND isv.table_name = pgc.relname
WHERE
pgc.relkind IN ('r',
'v',
'f',
'm',
'p')
AND ((pgn.nspname='servicedesk'))
GROUP BY pgc.oid,
pgn.nspname,
pgc.relname,
table_type,
isv.*) AS info;
Aggregate (cost=584.58..584.59 rows=1 width=32)
-> Subquery Scan on info (cost=578.68..584.54 rows=7 width=152)
-> GroupAggregate (cost=578.68..584.47 rows=7 width=324)
Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
-> Sort (cost=578.68..578.70 rows=7 width=582)
Sort Key: pgc.oid, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
-> Nested Loop Left Join (cost=390.47..578.58 rows=7 width=582)
Join Filter: ((nc_1.nspname = pgn.nspname) AND (c_1.relname = pgc.relname))
-> Nested Loop Left Join (cost=349.13..469.16 rows=7 width=519)
Join Filter: (nc.nspname = pgn.nspname)
-> Nested Loop Left Join (cost=346.35..441.12 rows=7 width=487)
-> Nested Loop Left Join (cost=346.07..439.99 rows=1 width=417)
Join Filter: (((ist.event_object_schema)::name = pgn.nspname) AND ((ist.event_object_table)::name = pgc.relname) AND ((ist.trigger_name)::name = pgt.tgname))
-> Nested Loop Left Join (cost=41.62..81.99 rows=1 width=201)
-> Nested Loop (cost=41.34..81.40 rows=1 width=133)
-> Index Scan using pg_namespace_nspname_index on pg_namespace pgn (cost=0.28..2.49 rows=1 width=68)
Index Cond: (nspname = 'servicedesk'::name)
-> Bitmap Heap Scan on pg_class pgc (cost=41.07..78.76 rows=14 width=73)
Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
-> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..41.06 rows=51 width=0)
Index Cond: (relnamespace = pgn.oid)
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pgt (cost=0.28..0.52 rows=7 width=72)
Index Cond: (tgrelid = pgc.oid)
-> Subquery Scan on ist (cost=304.45..357.95 rows=3 width=408)
Filter: ((ist.event_object_schema)::name = 'servicedesk'::name)
-> WindowAgg (cost=304.45..351.43 rows=522 width=888)
-> Sort (cost=304.45..305.75 rows=522 width=438)
Sort Key: ((n.nspname)::information_schema.sql_identifier), ((c_2.relname)::information_schema.sql_identifier), "*VALUES*".column1, (((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
-> Nested Loop (cost=192.35..280.88 rows=522 width=438)
Join Filter: (((t_1.tgtype)::integer & "*VALUES*".column1) <> 0)
-> Hash Join (cost=192.35..265.12 rows=175 width=330)
Hash Cond: (t_1.tgrelid = c_2.oid)
-> Seq Scan on pg_trigger t_1 (cost=0.00..69.62 rows=374 width=202)
Filter: (NOT tgisinternal)
-> Hash (cost=178.52..178.52 rows=1106 width=136)
-> Hash Join (cost=22.81..178.52 rows=1106 width=136)
Hash Cond: (c_2.relnamespace = n.oid)
-> Seq Scan on pg_class c_2 (cost=0.00..151.32 rows=1659 width=76)
Filter: (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE, REFERENCES'::text))
-> Hash (cost=17.29..17.29 rows=442 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..17.29 rows=442 width=68)
Filter: (NOT pg_is_other_temp_schema(oid))
-> Materialize (cost=0.00..0.05 rows=3 width=36)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.29..1.06 rows=7 width=70)
Index Cond: (attrelid = pgc.oid)
-> Nested Loop Left Join (cost=2.77..3.99 rows=1 width=224)
-> Nested Loop (cost=2.22..3.36 rows=1 width=228)
-> Nested Loop (cost=1.95..3.03 rows=1 width=627)
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..0.44 rows=1 width=76)
Index Cond: (relname = pgc.relname)
Filter: (relkind = ANY ('{r,v,m,f,p}'::"char"[]))
-> Nested Loop (cost=1.67..2.58 rows=1 width=571)
-> Nested Loop Left Join (cost=1.39..2.25 rows=1 width=503)
-> Nested Loop Left Join (cost=1.11..1.95 rows=1 width=294)
Join Filter: (t.typtype = 'd'::"char")
-> Nested Loop (cost=0.56..1.31 rows=1 width=160)
-> Index Scan using pg_attribute_relid_attnam_index on pg_attribute a (cost=0.29..1.01 rows=1 width=80)
Index Cond: ((attrelid = c.oid) AND (attname = pga.attname))
Filter: ((NOT attisdropped) AND (attnum > 0) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.28..0.30 rows=1 width=84)
Index Cond: (oid = a.atttypid)
-> Nested Loop (cost=0.55..0.63 rows=1 width=138)
-> Index Scan using pg_type_oid_index on pg_type bt (cost=0.28..0.33 rows=1 width=78)
Index Cond: (oid = t.typbasetype)
-> Index Scan using pg_namespace_oid_index on pg_namespace nbt (cost=0.28..0.30 rows=1 width=68)
Index Cond: (oid = bt.typnamespace)
-> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.28..0.30 rows=1 width=211)
Index Cond: ((adrelid = a.attrelid) AND (adnum = a.attnum))
-> Index Scan using pg_namespace_oid_index on pg_namespace nc (cost=0.28..0.32 rows=1 width=68)
Index Cond: (oid = c.relnamespace)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
-> Index Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.28..0.30 rows=1 width=68)
Index Cond: (oid = t.typnamespace)
-> Nested Loop (cost=0.55..0.62 rows=1 width=4)
-> Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..0.30 rows=1 width=72)
Index Cond: (oid = a.attcollation)
-> Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.28..0.32 rows=1 width=68)
Index Cond: (oid = co.collnamespace)
Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
-> Materialize (cost=41.34..109.21 rows=1 width=160)
-> Nested Loop (cost=41.34..109.21 rows=1 width=160)
-> Index Scan using pg_namespace_nspname_index on pg_namespace nc_1 (cost=0.28..2.50 rows=1 width=68)
Index Cond: (nspname = 'servicedesk'::name)
Filter: (NOT pg_is_other_temp_schema(oid))
-> Bitmap Heap Scan on pg_class c_1 (cost=41.07..80.10 rows=2 width=76)
Recheck Cond: (relnamespace = nc_1.oid)
Filter: ((relkind = ANY ('{v,m}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..41.06 rows=51 width=0)
Index Cond: (relnamespace = nc_1.oid)
SubPlan 1
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger (cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 81) = 81)
SubPlan 2
-> Seq Scan on pg_trigger pg_trigger_1 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer & 81) = 81)
SubPlan 3
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_2 (cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 73) = 73)
SubPlan 4
-> Seq Scan on pg_trigger pg_trigger_3 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer & 73) = 73)
SubPlan 5
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_4 (cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 69) = 69)
SubPlan 6
-> Seq Scan on pg_trigger pg_trigger_5 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer & 69) = 69)
-> Subquery Scan on info (cost=578.68..584.54 rows=7 width=152)
-> GroupAggregate (cost=578.68..584.47 rows=7 width=324)
Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
-> Sort (cost=578.68..578.70 rows=7 width=582)
Sort Key: pgc.oid, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
-> Nested Loop Left Join (cost=390.47..578.58 rows=7 width=582)
Join Filter: ((nc_1.nspname = pgn.nspname) AND (c_1.relname = pgc.relname))
-> Nested Loop Left Join (cost=349.13..469.16 rows=7 width=519)
Join Filter: (nc.nspname = pgn.nspname)
-> Nested Loop Left Join (cost=346.35..441.12 rows=7 width=487)
-> Nested Loop Left Join (cost=346.07..439.99 rows=1 width=417)
Join Filter: (((ist.event_object_schema)::name = pgn.nspname) AND ((ist.event_object_table)::name = pgc.relname) AND ((ist.trigger_name)::name = pgt.tgname))
-> Nested Loop Left Join (cost=41.62..81.99 rows=1 width=201)
-> Nested Loop (cost=41.34..81.40 rows=1 width=133)
-> Index Scan using pg_namespace_nspname_index on pg_namespace pgn (cost=0.28..2.49 rows=1 width=68)
Index Cond: (nspname = 'servicedesk'::name)
-> Bitmap Heap Scan on pg_class pgc (cost=41.07..78.76 rows=14 width=73)
Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
-> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..41.06 rows=51 width=0)
Index Cond: (relnamespace = pgn.oid)
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pgt (cost=0.28..0.52 rows=7 width=72)
Index Cond: (tgrelid = pgc.oid)
-> Subquery Scan on ist (cost=304.45..357.95 rows=3 width=408)
Filter: ((ist.event_object_schema)::name = 'servicedesk'::name)
-> WindowAgg (cost=304.45..351.43 rows=522 width=888)
-> Sort (cost=304.45..305.75 rows=522 width=438)
Sort Key: ((n.nspname)::information_schema.sql_identifier), ((c_2.relname)::information_schema.sql_identifier), "*VALUES*".column1, (((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
-> Nested Loop (cost=192.35..280.88 rows=522 width=438)
Join Filter: (((t_1.tgtype)::integer & "*VALUES*".column1) <> 0)
-> Hash Join (cost=192.35..265.12 rows=175 width=330)
Hash Cond: (t_1.tgrelid = c_2.oid)
-> Seq Scan on pg_trigger t_1 (cost=0.00..69.62 rows=374 width=202)
Filter: (NOT tgisinternal)
-> Hash (cost=178.52..178.52 rows=1106 width=136)
-> Hash Join (cost=22.81..178.52 rows=1106 width=136)
Hash Cond: (c_2.relnamespace = n.oid)
-> Seq Scan on pg_class c_2 (cost=0.00..151.32 rows=1659 width=76)
Filter: (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE, REFERENCES'::text))
-> Hash (cost=17.29..17.29 rows=442 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..17.29 rows=442 width=68)
Filter: (NOT pg_is_other_temp_schema(oid))
-> Materialize (cost=0.00..0.05 rows=3 width=36)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.29..1.06 rows=7 width=70)
Index Cond: (attrelid = pgc.oid)
-> Nested Loop Left Join (cost=2.77..3.99 rows=1 width=224)
-> Nested Loop (cost=2.22..3.36 rows=1 width=228)
-> Nested Loop (cost=1.95..3.03 rows=1 width=627)
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..0.44 rows=1 width=76)
Index Cond: (relname = pgc.relname)
Filter: (relkind = ANY ('{r,v,m,f,p}'::"char"[]))
-> Nested Loop (cost=1.67..2.58 rows=1 width=571)
-> Nested Loop Left Join (cost=1.39..2.25 rows=1 width=503)
-> Nested Loop Left Join (cost=1.11..1.95 rows=1 width=294)
Join Filter: (t.typtype = 'd'::"char")
-> Nested Loop (cost=0.56..1.31 rows=1 width=160)
-> Index Scan using pg_attribute_relid_attnam_index on pg_attribute a (cost=0.29..1.01 rows=1 width=80)
Index Cond: ((attrelid = c.oid) AND (attname = pga.attname))
Filter: ((NOT attisdropped) AND (attnum > 0) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.28..0.30 rows=1 width=84)
Index Cond: (oid = a.atttypid)
-> Nested Loop (cost=0.55..0.63 rows=1 width=138)
-> Index Scan using pg_type_oid_index on pg_type bt (cost=0.28..0.33 rows=1 width=78)
Index Cond: (oid = t.typbasetype)
-> Index Scan using pg_namespace_oid_index on pg_namespace nbt (cost=0.28..0.30 rows=1 width=68)
Index Cond: (oid = bt.typnamespace)
-> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.28..0.30 rows=1 width=211)
Index Cond: ((adrelid = a.attrelid) AND (adnum = a.attnum))
-> Index Scan using pg_namespace_oid_index on pg_namespace nc (cost=0.28..0.32 rows=1 width=68)
Index Cond: (oid = c.relnamespace)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
-> Index Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.28..0.30 rows=1 width=68)
Index Cond: (oid = t.typnamespace)
-> Nested Loop (cost=0.55..0.62 rows=1 width=4)
-> Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..0.30 rows=1 width=72)
Index Cond: (oid = a.attcollation)
-> Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.28..0.32 rows=1 width=68)
Index Cond: (oid = co.collnamespace)
Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
-> Materialize (cost=41.34..109.21 rows=1 width=160)
-> Nested Loop (cost=41.34..109.21 rows=1 width=160)
-> Index Scan using pg_namespace_nspname_index on pg_namespace nc_1 (cost=0.28..2.50 rows=1 width=68)
Index Cond: (nspname = 'servicedesk'::name)
Filter: (NOT pg_is_other_temp_schema(oid))
-> Bitmap Heap Scan on pg_class c_1 (cost=41.07..80.10 rows=2 width=76)
Recheck Cond: (relnamespace = nc_1.oid)
Filter: ((relkind = ANY ('{v,m}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..41.06 rows=51 width=0)
Index Cond: (relnamespace = nc_1.oid)
SubPlan 1
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger (cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 81) = 81)
SubPlan 2
-> Seq Scan on pg_trigger pg_trigger_1 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer & 81) = 81)
SubPlan 3
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_2 (cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 73) = 73)
SubPlan 4
-> Seq Scan on pg_trigger pg_trigger_3 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer & 73) = 73)
SubPlan 5
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_4 (cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 69) = 69)
SubPlan 6
-> Seq Scan on pg_trigger pg_trigger_5 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer & 69) = 69)
PostgreSQL 13.14 (Ubuntu 13.14-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
On Wed, 15 May 2024 at 21:08, Sašo Gantar <sasog23@gmail.com> wrote: > this query takes more than 8 seconds, > if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't very useful as there's no way to tell if the planner's estimates were accurate or not. Also with EXPLAIN only, we don't know where the time was spent in the query. Running the EXPLAIN with "SET track_io_timing = 1;" would be even more useful. David
Aggregate (cost=512.53..512.54 rows=1 width=32) (actual time=8430.692..8430.724 rows=1 loops=1)
Buffers: shared hit=2031540, temp read=954 written=956
-> Subquery Scan on info (cost=510.85..512.52 rows=2 width=152) (actual time=8257.310..8430.532 rows=57 loops=1)
Buffers: shared hit=2031540, temp read=954 written=956
-> GroupAggregate (cost=510.85..512.50 rows=2 width=324) (actual time=8257.304..8430.427 rows=57 loops=1)
Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
Buffers: shared hit=2031540, temp read=954 written=956
-> Sort (cost=510.85..510.85 rows=2 width=582) (actual time=8253.824..8258.370 rows=12994 loops=1)
Sort Key: pgc.oid, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
Sort Method: external merge Disk: 7632kB
Buffers: shared hit=2004085, temp read=954 written=956
-> Nested Loop Left Join (cost=353.35..510.84 rows=2 width=582) (actual time=25.558..8232.211 rows=12994 loops=1)
Join Filter: (nc_1.nspname = pgn.nspname)
Buffers: shared hit=2004074
-> Nested Loop Left Join (cost=352.79..455.74 rows=2 width=519) (actual time=25.548..8207.051 rows=12994 loops=1)
Join Filter: (nc.nspname = pgn.nspname)
Buffers: shared hit=1963631
-> Nested Loop Left Join (cost=350.04..447.39 rows=2 width=487) (actual time=25.527..8049.285 rows=12994 loops=1)
Buffers: shared hit=1719586
-> Nested Loop Left Join (cost=349.76..446.26 rows=1 width=417) (actual time=25.509..8039.922 rows=429 loops=1)
Join Filter: (((ist.event_object_schema)::name = pgn.nspname) AND ((ist.event_object_table)::name = pgc.relname) AND ((ist.trigger_name)::name = pgt.tgname))
Rows Removed by Join Filter: 43600
Buffers: shared hit=1711154
-> Nested Loop Left Join (cost=39.75..81.15 rows=1 width=201) (actual time=0.209..1.186 rows=401 loops=1)
Buffers: shared hit=329
-> Nested Loop (cost=39.47..80.56 rows=1 width=133) (actual time=0.179..0.475 rows=57 loops=1)
Buffers: shared hit=65
-> Index Scan using pg_namespace_nspname_index on pg_namespace pgn (cost=0.28..2.49 rows=1 width=68) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (nspname = 'servicedesk'::name)
Buffers: shared hit=3
-> Bitmap Heap Scan on pg_class pgc (cost=39.19..77.93 rows=14 width=73) (actual time=0.171..0.432 rows=57 loops=1)
Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
Rows Removed by Filter: 163
Heap Blocks: exact=44
Buffers: shared hit=62
-> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..39.19 rows=53 width=0) (actual time=0.158..0.158 rows=220 loops=1)
Index Cond: (relnamespace = pgn.oid)
Buffers: shared hit=18
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pgt (cost=0.28..0.52 rows=7 width=72) (actual time=0.005..0.009 rows=7 loops=57)
Index Cond: (tgrelid = pgc.oid)
Buffers: shared hit=264
-> Subquery Scan on ist (cost=310.01..365.05 rows=3 width=408) (actual time=11.344..20.034 rows=109 loops=401)
Filter: ((ist.event_object_schema)::name = 'servicedesk'::name)
Rows Removed by Filter: 364
Buffers: shared hit=1710825
-> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401)
Buffers: shared hit=1710825
-> Sort (cost=310.01..311.35 rows=537 width=438) (actual time=0.008..0.042 rows=473 loops=401)
Sort Key: ((n.nspname)::information_schema.sql_identifier), ((c_2.relname)::information_schema.sql_identifier), "*VALUES*".column1, (((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
Sort Method: quicksort Memory: 268kB
Buffers: shared hit=2056
-> Nested Loop (cost=195.66..285.66 rows=537 width=438) (actual time=2.143..2.752 rows=473 loops=1)
Join Filter: (((t_1.tgtype)::integer & "*VALUES*".column1) <> 0)
Rows Removed by Join Filter: 679
Buffers: shared hit=2053
-> Hash Join (cost=195.66..269.45 rows=180 width=330) (actual time=2.136..2.532 rows=384 loops=1)
Hash Cond: (t_1.tgrelid = c_2.oid)
Buffers: shared hit=2053
-> Seq Scan on pg_trigger t_1 (cost=0.00..70.55 rows=383 width=202) (actual time=0.005..0.313 rows=384 loops=1)
Filter: (NOT tgisinternal)
Rows Removed by Filter: 1872
Buffers: shared hit=48
-> Hash (cost=181.57..181.57 rows=1127 width=136) (actual time=2.120..2.123 rows=2401 loops=1)
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 426kB
Buffers: shared hit=2005
-> Hash Join (cost=23.06..181.57 rows=1127 width=136) (actual time=0.892..1.695 rows=2401 loops=1)
Hash Cond: (c_2.relnamespace = n.oid)
Buffers: shared hit=2005
-> Seq Scan on pg_class c_2 (cost=0.00..154.04 rows=1690 width=76) (actual time=0.003..0.449 rows=2401 loops=1)
Filter: (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE, REFERENCES'::text))
Buffers: shared hit=58
-> Hash (cost=17.44..17.44 rows=450 width=68) (actual time=0.868..0.869 rows=47 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=1947
-> Seq Scan on pg_namespace n (cost=0.00..17.44 rows=450 width=68) (actual time=0.003..0.859 rows=47 loops=1)
Filter: (NOT pg_is_other_temp_schema(oid))
Rows Removed by Filter: 642
Buffers: shared hit=1947
-> Materialize (cost=0.00..0.05 rows=3 width=36) (actual time=0.000..0.000 rows=3 loops=384)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (actual time=0.003..0.004 rows=3 loops=1)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.28..1.06 rows=7 width=70) (actual time=0.005..0.016 rows=30 loops=429)
Index Cond: (attrelid = pgc.oid)
Buffers: shared hit=8432
-> Nested Loop Left Join (cost=2.76..4.16 rows=1 width=224) (actual time=0.011..0.012 rows=1 loops=12994)
Buffers: shared hit=244045
-> Nested Loop (cost=2.21..3.52 rows=1 width=228) (actual time=0.010..0.011 rows=1 loops=12994)
Buffers: shared hit=219133
-> Nested Loop (cost=1.93..3.17 rows=1 width=627) (actual time=0.006..0.007 rows=1 loops=12994)
Buffers: shared hit=187599
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..0.44 rows=1 width=76) (actual time=0.001..0.001 rows=1 loops=12994)
Index Cond: (relname = pgc.relname)
Filter: (relkind = ANY ('{r,v,m,f,p}'::"char"[]))
Buffers: shared hit=39712
-> Nested Loop (cost=1.65..2.72 rows=1 width=571) (actual time=0.005..0.005 rows=1 loops=13724)
Buffers: shared hit=147887
-> Nested Loop Left Join (cost=1.38..2.39 rows=1 width=503) (actual time=0.004..0.004 rows=1 loops=13724)
Buffers: shared hit=116864
-> Nested Loop Left Join (cost=1.10..2.07 rows=1 width=294) (actual time=0.003..0.003 rows=1 loops=13724)
Join Filter: (t.typtype = 'd'::"char")
Buffers: shared hit=92576
-> Nested Loop (cost=0.55..1.32 rows=1 width=160) (actual time=0.002..0.002 rows=1 loops=13724)
Buffers: shared hit=71866
-> Index Scan using pg_attribute_relid_attnam_index on pg_attribute a (cost=0.28..1.00 rows=1 width=80) (actual time=0.001..0.001 rows=1 loops=13724)
Index Cond: ((attrelid = c.oid) AND (attname = pga.attname))
Filter: ((NOT attisdropped) AND (attnum > 0) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=40843
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.27..0.32 rows=1 width=84) (actual time=0.001..0.001 rows=1 loops=10341)
Index Cond: (oid = a.atttypid)
Buffers: shared hit=31023
-> Nested Loop (cost=0.55..0.74 rows=1 width=138) (actual time=0.001..0.001 rows=0 loops=10341)
Buffers: shared hit=20710
-> Index Scan using pg_type_oid_index on pg_type bt (cost=0.27..0.42 rows=1 width=78) (actual time=0.001..0.001 rows=0 loops=10341)
Index Cond: (oid = t.typbasetype)
Buffers: shared hit=20689
-> Index Scan using pg_namespace_oid_index on pg_namespace nbt (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=7)
Index Cond: (oid = bt.typnamespace)
Buffers: shared hit=21
-> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.28..0.32 rows=1 width=211) (actual time=0.001..0.001 rows=0 loops=10341)
Index Cond: ((adrelid = a.attrelid) AND (adnum = a.attnum))
Buffers: shared hit=24288
-> Index Scan using pg_namespace_oid_index on pg_namespace nc (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=10341)
Index Cond: (oid = c.relnamespace)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
Rows Removed by Filter: 0
Buffers: shared hit=31023
-> Index Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=10150)
Index Cond: (oid = t.typnamespace)
Buffers: shared hit=30450
-> Nested Loop (cost=0.55..0.63 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10150)
Buffers: shared hit=24912
-> Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..0.31 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=10150)
Index Cond: (oid = a.attcollation)
Buffers: shared hit=21453
-> Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1153)
Index Cond: (oid = co.collnamespace)
Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
Rows Removed by Filter: 1
Buffers: shared hit=3459
-> Nested Loop (cost=0.56..27.52 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=12994)
Buffers: shared hit=40443
-> Index Scan using pg_class_relname_nsp_index on pg_class c_1 (cost=0.28..0.46 rows=1 width=76) (actual time=0.001..0.001 rows=0 loops=12994)
Index Cond: (relname = pgc.relname)
Filter: ((relkind = ANY ('{v,m}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 1
Buffers: shared hit=39712
-> Index Scan using pg_namespace_oid_index on pg_namespace nc_1 (cost=0.28..0.43 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=9)
Index Cond: (oid = c_1.relnamespace)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
Buffers: shared hit=27
SubPlan 1
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 81) = 81)
Buffers: shared hit=18
SubPlan 2
-> Seq Scan on pg_trigger pg_trigger_1 (cost=0.00..87.46 rows=11 width=4) (never executed)
Filter: (((tgtype)::integer & 81) = 81)
SubPlan 3
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_2 (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 73) = 73)
Buffers: shared hit=18
SubPlan 4
-> Seq Scan on pg_trigger pg_trigger_3 (cost=0.00..87.46 rows=11 width=4) (never executed)
Filter: (((tgtype)::integer & 73) = 73)
SubPlan 5
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_4 (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 69) = 69)
Buffers: shared hit=18
SubPlan 6
-> Seq Scan on pg_trigger pg_trigger_5 (cost=0.00..87.46 rows=11 width=4) (never executed)
Filter: (((tgtype)::integer & 69) = 69)
Planning:
Buffers: shared hit=498
Planning Time: 7.419 ms
Execution Time: 8432.371 ms
what helps is
SET enable_nestloop = off;
query takes less then 2seconds
but it's probably not a good idea to change this flag
SET enable_nestloop = off;
query takes less then 2seconds
but it's probably not a good idea to change this flag
On Wed, 15 May 2024 at 13:23, David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 15 May 2024 at 21:08, Sašo Gantar <sasog23@gmail.com> wrote:
> this query takes more than 8 seconds,
> if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't
very useful as there's no way to tell if the planner's estimates were
accurate or not. Also with EXPLAIN only, we don't know where the time
was spent in the query.
Running the EXPLAIN with "SET track_io_timing = 1;" would be even more useful.
David
On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote:
[execution plan without query text or explanation]
The time is lost here:
-> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401)
Buffers: shared hit=1710825
Yours,
Laurenz Albe
On Mon, 20 May 2024 at 23:09, Sašo Gantar <sasog23@gmail.com> wrote:
> what helps is
> SET enable_nestloop = off;
> query takes less then 2seconds
> but it's probably not a good idea to change this flag
Looks like it's slow due to a bad selectivity estimate on the join
between pgn and pgc. This results in:
-> Nested Loop (cost=39.47..80.56 rows=1 width=133) (actual
time=0.179..0.475 rows=57 loops=1)
because the row estimate is 1, from there down to the root of the plan
tree the planner thinks Nested Loop is a good join type for the
remaining joins. Unfortunately, it's wrong.
I don't really see a good way to convince the planner not to do this.
The problem condition is:
Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
if ANALYZE pg_class; does not help then you could maybe mess with the
n_distinct estimate on pg_class.relnamespace, but you risk making
other queries worse.
Disabling enable_nestloop might be a good option, if you can just do
it for this query. Unfortunately, parameterized nested loops are also
tied into that GUC, so you'll stop those working for this plan. The
Nested Loop between pgn and pgc looks like a good choice. The rest,
not so much.
I don't think (ndistinct) extended statistics on pg_class
relnamespace, relkind will help since "relnamespace = pgn.oid" is a
join condition.
David
thanks for the info, but is there any solution, given that it's system tables?
regards
On Tue, 21 May 2024 at 12:09, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote:
[execution plan without query text or explanation]
The time is lost here:
-> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401)
Buffers: shared hit=1710825
Yours,
Laurenz Albe
On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote: > thanks for the info, but is there any solution, given that it's system tables? We still don't know the query. Yours, Laurenz Albe
On Tue, 21 May 2024 at 23:14, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > We still don't know the query. hmm, it was posted on this thread: https://postgr.es/m/CAGB0_6600w5C=hvhgfMWCqO9BCwCg+3s0PxXuoQv48NLqTp6dA@mail.gmail.com David
sorry...
SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES
FROM
(WITH partitions AS
(SELECT array
(WITH partitioned_tables AS
(SELECT array
(SELECT oid
FROM pg_class
WHERE relkind = 'p') AS parent_tables) SELECT child.relname AS PARTITION
FROM partitioned_tables, pg_inherits
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE
((nmsp_child.nspname='servicedesk'))
AND pg_inherits.inhparent = ANY (partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS table_schema,
pgc.relname AS TABLE_NAME,
CASE
WHEN pgc.relkind = 'r' THEN 'TABLE'
WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'
WHEN pgc.relkind = 'v' THEN 'VIEW'
WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'
WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'
END AS table_type,
obj_description(pgc.oid) AS COMMENT,
COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (
WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns,
COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB || jsonb_build_object('comment', obj_description(pgt.oid))) filter (
WHERE ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS,
row_to_json(isv) AS view_info
FROM partitions,
pg_class AS pgc
INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns */ /* This is a simplified version of how information_schema.columns was ** implemented in postgres 9.5, but modified to support materialized ** views. */
LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
a.attname AS COLUMN_NAME,
a.attnum AS ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
CASE
WHEN a.attnotnull
OR (t.typtype = 'd'
AND t.typnotnull) THEN 'NO'
ELSE 'YES'
END AS is_nullable,
CASE
WHEN t.typtype = 'd' THEN CASE
WHEN bt.typelem <> 0
AND bt.typlen = -1 THEN 'ARRAY'
WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, NULL)
ELSE 'USER-DEFINED'
END
ELSE CASE
WHEN t.typelem <> 0
AND t.typlen = -1 THEN 'ARRAY'
WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, NULL)
ELSE 'USER-DEFINED'
END
END AS data_type,
coalesce(bt.typname, t.typname) AS data_type_name,
CASE
WHEN a.attidentity = 'd' THEN TRUE
WHEN a.attidentity = 'a' THEN TRUE
ELSE FALSE
END AS is_identity
FROM (pg_attribute a
LEFT JOIN pg_attrdef ad ON attrelid = adrelid
AND attnum = adnum)
JOIN (pg_class c
JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
JOIN (pg_type t
JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd'
AND t.typbasetype = bt.oid)
LEFT JOIN (pg_collation co
JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON a.attcollation = co.oid
AND (nco.nspname,
co.collname) <> ('pg_catalog',
'default')
WHERE (NOT pg_is_other_temp_schema(nc.oid))
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relkind in ('r',
'v',
'm',
'f',
'p')
AND (pg_has_role(c.relowner, 'USAGE')
OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname
AND isc.table_name = pgc.relname
AND isc.column_name = pga.attname /* triggers */
LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid
LEFT OUTER JOIN information_schema.triggers AS ist ON ist.event_object_schema = pgn.nspname
AND ist.event_object_table = pgc.relname
AND ist.trigger_name = pgt.tgname /* This is a simplified version of how information_schema.views was ** implemented in postgres 9.5, but modified to support materialized ** views. */
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
CASE
WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid)
ELSE NULL
END AS view_definition,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN 'YES'
ELSE 'NO'
END AS is_updatable,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN 'YES'
ELSE 'NO'
END AS is_insertable_into,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 81 = 81) THEN 'YES'
ELSE 'NO'
END AS is_trigger_updatable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 73 = 73) THEN 'YES'
ELSE 'NO'
END AS is_trigger_deletable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 69 = 69) THEN 'YES'
ELSE 'NO'
END AS is_trigger_insertable_into
FROM pg_namespace nc,
pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind in ('v',
'm')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname
AND isv.table_name = pgc.relname
WHERE
pgc.relkind IN ('r',
'v',
'f',
'm',
'p')
AND ((pgn.nspname='servicedesk'))
GROUP BY pgc.oid,
pgn.nspname,
pgc.relname,
table_type,
isv.*) AS info;
FROM
(WITH partitions AS
(SELECT array
(WITH partitioned_tables AS
(SELECT array
(SELECT oid
FROM pg_class
WHERE relkind = 'p') AS parent_tables) SELECT child.relname AS PARTITION
FROM partitioned_tables, pg_inherits
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE
((nmsp_child.nspname='servicedesk'))
AND pg_inherits.inhparent = ANY (partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS table_schema,
pgc.relname AS TABLE_NAME,
CASE
WHEN pgc.relkind = 'r' THEN 'TABLE'
WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'
WHEN pgc.relkind = 'v' THEN 'VIEW'
WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'
WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'
END AS table_type,
obj_description(pgc.oid) AS COMMENT,
COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB || jsonb_build_object('comment', col_description(pga.attrelid, pga.attnum))) filter (
WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns,
COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB || jsonb_build_object('comment', obj_description(pgt.oid))) filter (
WHERE ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS,
row_to_json(isv) AS view_info
FROM partitions,
pg_class AS pgc
INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns */ /* This is a simplified version of how information_schema.columns was ** implemented in postgres 9.5, but modified to support materialized ** views. */
LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
a.attname AS COLUMN_NAME,
a.attnum AS ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
CASE
WHEN a.attnotnull
OR (t.typtype = 'd'
AND t.typnotnull) THEN 'NO'
ELSE 'YES'
END AS is_nullable,
CASE
WHEN t.typtype = 'd' THEN CASE
WHEN bt.typelem <> 0
AND bt.typlen = -1 THEN 'ARRAY'
WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, NULL)
ELSE 'USER-DEFINED'
END
ELSE CASE
WHEN t.typelem <> 0
AND t.typlen = -1 THEN 'ARRAY'
WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, NULL)
ELSE 'USER-DEFINED'
END
END AS data_type,
coalesce(bt.typname, t.typname) AS data_type_name,
CASE
WHEN a.attidentity = 'd' THEN TRUE
WHEN a.attidentity = 'a' THEN TRUE
ELSE FALSE
END AS is_identity
FROM (pg_attribute a
LEFT JOIN pg_attrdef ad ON attrelid = adrelid
AND attnum = adnum)
JOIN (pg_class c
JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
JOIN (pg_type t
JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd'
AND t.typbasetype = bt.oid)
LEFT JOIN (pg_collation co
JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON a.attcollation = co.oid
AND (nco.nspname,
co.collname) <> ('pg_catalog',
'default')
WHERE (NOT pg_is_other_temp_schema(nc.oid))
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relkind in ('r',
'v',
'm',
'f',
'p')
AND (pg_has_role(c.relowner, 'USAGE')
OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname
AND isc.table_name = pgc.relname
AND isc.column_name = pga.attname /* triggers */
LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid
LEFT OUTER JOIN information_schema.triggers AS ist ON ist.event_object_schema = pgn.nspname
AND ist.event_object_table = pgc.relname
AND ist.trigger_name = pgt.tgname /* This is a simplified version of how information_schema.views was ** implemented in postgres 9.5, but modified to support materialized ** views. */
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
CASE
WHEN pg_has_role(c.relowner, 'USAGE') THEN pg_get_viewdef(c.oid)
ELSE NULL
END AS view_definition,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN 'YES'
ELSE 'NO'
END AS is_updatable,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN 'YES'
ELSE 'NO'
END AS is_insertable_into,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 81 = 81) THEN 'YES'
ELSE 'NO'
END AS is_trigger_updatable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 73 = 73) THEN 'YES'
ELSE 'NO'
END AS is_trigger_deletable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 69 = 69) THEN 'YES'
ELSE 'NO'
END AS is_trigger_insertable_into
FROM pg_namespace nc,
pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind in ('v',
'm')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname
AND isv.table_name = pgc.relname
WHERE
pgc.relkind IN ('r',
'v',
'f',
'm',
'p')
AND ((pgn.nspname='servicedesk'))
GROUP BY pgc.oid,
pgn.nspname,
pgc.relname,
table_type,
isv.*) AS info;
On Tue, 21 May 2024 at 13:14, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote:
> thanks for the info, but is there any solution, given that it's system tables?
We still don't know the query.
Yours,
Laurenz Albe
=?UTF-8?B?U2HFoW8gR2FudGFy?= <sasog23@gmail.com> writes:
> thanks for the info, but is there any solution, given that it's system
> tables?
Given the complexity of the query, I wonder if you're running into
problems with join_collapse_limit/from_collapse_limit preventing
the planner from considering all options.
Also, as David already mentioned, ANALYZE on pg_class might help.
regards, tom lane
ANALYZE pg_class; doesn't help
also, query is from "Hasura", so I don't have much room to maneuver
On Tue, 21 May 2024 at 16:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Sašo Gantar <sasog23@gmail.com> writes:
> thanks for the info, but is there any solution, given that it's system
> tables?
Given the complexity of the query, I wonder if you're running into
problems with join_collapse_limit/from_collapse_limit preventing
the planner from considering all options.
Also, as David already mentioned, ANALYZE on pg_class might help.
regards, tom lane
upgrade to "PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit"
solved the problem
regards,
s
On Wed, 22 May 2024 at 06:04, Sašo Gantar <sasog23@gmail.com> wrote:
ANALYZE pg_class; doesn't helpalso, query is from "Hasura", so I don't have much room to maneuverOn Tue, 21 May 2024 at 16:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:Sašo Gantar <sasog23@gmail.com> writes:
> thanks for the info, but is there any solution, given that it's system
> tables?
Given the complexity of the query, I wonder if you're running into
problems with join_collapse_limit/from_collapse_limit preventing
the planner from considering all options.
Also, as David already mentioned, ANALYZE on pg_class might help.
regards, tom lane