problem with query
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
Re: problem with query
> > > 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) >
Re: problem with query
what helps is 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 wrote: > On Wed, 15 May 2024 at 21:08, Sašo Gantar 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 >
Re: problem with query
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 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 >
Re: problem with query
;)
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 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
>
Re: problem with query
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 wrote: > =?UTF-8?B?U2HFoW8gR2FudGFy?= 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 >
Re: problem with query
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 wrote: > 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 wrote: > >> =?UTF-8?B?U2HFoW8gR2FudGFy?= 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 >> >
