Home > mailing lists

problem with query - Mailing list pgsql-general

From	Sašo Gantar
Subject	problem with query
Date	May 15 11:07:12
Msg-id	CAGB0_6600w5C=hvhgfMWCqO9BCwCg+3s0PxXuoQv48NLqTp6dA@mail.gmail.com Whole thread Raw
Responses	Re: problem with query
List	pgsql-general

Tree view

this query takes more than 8 seconds,

if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s

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;

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)

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

pgsql-general by date:

From: Danut Soare
Date: 15 May, 11:03:05
Subject: Sequence values change during upgrade

From: HORDER Philip
Date: 15 May, 11:08:48
Subject: RE: Restore of a reference database kills the auto analyze processing.

problem with query - Mailing list pgsql-general

Previous

Next