text:
Q: Slow queries on system tables One of our monitoring system suspected below query is causing for DB high load. Is there any possibility to improve the performance on systems tables .
SELECT NULL AS TABLE_CAT,
       n.nspname AS TABLE_SCHEM,
       c.relname AS TABLE_NAME,
       CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'
          WHEN true
          THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema'
                    THEN CASE c.relkind
                            WHEN 'r' THEN 'SYSTEM TABLE'
                            WHEN 'v' THEN 'SYSTEM VIEW'
                            WHEN 'i' THEN 'SYSTEM INDEX'
                            ELSE NULL
                         END
                    WHEN n.nspname = 'pg_toast'
                    THEN CASE c.relkind
                            WHEN 'r' THEN 'SYSTEM TOAST TABLE'
                            WHEN 'i' THEN 'SYSTEM TOAST INDEX'
                            ELSE NULL
                         END
                    ELSE CASE c.relkind
                            WHEN 'r' THEN 'TEMPORARY TABLE'
                            WHEN 'p' THEN 'TEMPORARY TABLE'
                            WHEN 'i' THEN 'TEMPORARY INDEX'
                            WHEN 'S' THEN 'TEMPORARY SEQUENCE'
                            WHEN 'v' THEN 'TEMPORARY VIEW'
                            ELSE NULL
                         END
               END
          WHEN false
          THEN CASE c.relkind
                  WHEN 'r' THEN 'TABLE'
                  WHEN 'p' THEN 'TABLE'
                  WHEN 'i' THEN 'INDEX'
                  WHEN 'S' THEN 'SEQUENCE'
                  WHEN 'v' THEN 'VIEW'
                  WHEN 'c' THEN 'TYPE'
                  WHEN 'f' THEN 'FOREIGN TABLE'
                  WHEN 'm' THEN 'MATERIALIZED VIEW'
                  ELSE NULL
               END
          ELSE NULL
       END AS TABLE_TYPE,
       d.description AS REMARKS,
       '' as TYPE_CAT,
       '' as TYPE_SCHEM,
       '' as TYPE_NAME,
       '' AS SELF_REFERENCING_COL_NAME,
       '' AS REF_GENERATION
FROM pg_catalog.pg_namespace n,
     pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_description d
           ON (c.oid = d.objoid AND d.objsubid = 0)
        LEFT JOIN pg_catalog.pg_class dc
           ON (d.classoid=dc.oid AND dc.relname='pg_class')
        LEFT JOIN pg_catalog.pg_namespace dn
           ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
WHERE c.relnamespace = n.oid
  AND n.nspname LIKE 'input_schemaname'
  AND c.relname LIKE 'input_tablename'
ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME;

A: What a silly query!
Simply remove the last two left joins, and it will have less useless work to do.
Perhaps your load is caused by the high number of tables in your database.


If you love fresh pastel tones and natural colours then a cottage style kitchen may be for you. Aspire Kitchen Design in Stirling has a fabulous range of cottage style kitchens which brings the country to your home. Timelessly beautiful, this style typically makes the kitchen the heart of the home. Quality German accessories and appliances will complement the cosy and bright feel making this a truly cosy place to entertain.