Subject: problem with query

From: Sašo Gantar <[email-masked]>
Date: Wed, 15 May 2024 10:07:12 +0200

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

---

From: David Rowley <[email-masked]>
Date: Wed, 15 May 2024 23:23:27 +1200

> 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

---

From: Sašo Gantar <[email-masked]>
Date: Mon, 20 May 2024 13:08:43 +0200

>
>
> 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)
>               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
>                     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)
>                                 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:
>                                             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
> rows=1 width=68) (actual time=0.006..0.007 rows=1 loops=1)
>                                                               Index Cond:
>                                                               Buffers:
> shared hit=3
> 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
>                                                               Rows Removed
> by Filter: 163
>                                                               Heap Blocks:
> exact=44
>                                                               Buffers:
> shared hit=62
> 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
> Buffers: shared hit=18
> 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:
>                                                   Rows Removed by Filter:
> 364
>                                                   Buffers: shared
> hit=1710825
>  (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:
>                                                               Sort Method:
> quicksort  Memory: 268kB
>                                                               Buffers:
> shared hit=2056
> Loop  (cost=195.66..285.66 rows=537 width=438) (actual time=2.143..2.752
> rows=473 loops=1)
>                                                                     Join
>                                                                     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)
>
> 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)
>
>       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)
>
>             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)
>
>                   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)
>
>                         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
> (actual time=0.001..0.001 rows=1 loops=12994)
>                                                         Index Cond:
> (relname = pgc.relname)
>                                                         Filter: (relkind =
>                                                         Buffers: shared
> hit=39712
>  (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
> 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
>
>  (cost=0.28..1.00 rows=1 width=80) (actual time=0.001..0.001 rows=1
> loops=13724)
>
>       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)
>
>       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)
>
>       Buffers: shared hit=20689
>
>  (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=7)
>
>       Buffers: shared hit=21
>  (cost=0.28..0.32 rows=1 width=211) (actual time=0.001..0.001 rows=0
> loops=10341)
>                                                                     Index
> Buffers: shared hit=24288
>                                                         ->  Index Scan
> 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
> width=68) (actual time=0.001..0.001 rows=0 loops=1153)
>                                                   Index Cond: (oid =
> co.collnamespace)
>                                                   Filter: ((nspname <>
>                                                   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
>                                       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
>                                       Buffers: shared hit=27
> 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
>  (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
>  (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
>

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 21 May 2024 12:09:19 +0200

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

---

From: Sašo Gantar <[email-masked]>
Date: Tue, 21 May 2024 12:49:08 +0200

thanks for the info, but is there any solution, given that it's system
tables?

regards

---

From: Tom Lane <[email-masked]>
Date: Tue, 21 May 2024 10:18:52 -0400

> thanks for the info, but is there any solution, given that it's system

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

---

From: Sašo Gantar <[email-masked]>
Date: Wed, 22 May 2024 06:04:56 +0200

ANALYZE pg_class; doesn't help
also, query is from "Hasura", so I don't have much room to maneuver

---

From: Sašo Gantar <[email-masked]>
Date: Mon, 27 May 2024 13:39:54 +0200

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