Search Postgresql Archives

problem with query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

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
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux