pg_dump_getIndexes.sql

-- Create 1.5k tables with 5 indexes each.
DO $$
DECLARE
    i INTEGER;
    j INTEGER;
BEGIN
    FOR i IN 1..1500 LOOP
        EXECUTE 'CREATE TABLE tab' || i || ' as SELECT 1 as f';
        FOR j IN 1..5 LOOP
            EXECUTE 'CREATE index idx_tab' ||  i ||  '_' || j || ' ON tab' || i || '(f)';
        END LOOP;
    END LOOP;
END;
$$;

-- Rewritten query to get rid of subqueries in the targetlist
EXPLAIN ANALYZE SELECT t.tableoid,
       t.oid,
       i.indrelid,
       t.relname AS indexname,
       t.relpages,
       t.reltuples,
       t.relallvisible,
       0 AS relallfrozen,
       pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef,
       i.indkey,
       i.indisclustered,
       c.contype,
       c.conname,
       c.condeferrable,
       c.condeferred,
       c.tableoid AS contableoid,
       c.oid AS conoid,
       pg_catalog.pg_get_constraintdef(c.oid, FALSE) AS condef,
       i.indattnames,
       s.spcname AS TABLESPACE,
       t.reloptions AS indreloptions,
       i.indisreplident,
       inh.inhparent AS parentidx,
       i.indnkeyatts AS indnkeyatts,
       i.indnatts AS indnatts,
       i.indstatcols,
       i.indstatvals,
       i.indnullsnotdistinct,
       NULL AS conperiod
FROM (SELECT i.indexrelid,
             i.indrelid,
             i.indkey,
             i.indisclustered,
             pg_catalog.array_agg(a.attnum ORDER BY a.attnum) FILTER (WHERE a.attstattarget >= 0) AS indstatcols,
             pg_catalog.array_agg(a.attstattarget ORDER BY a.attnum) FILTER (WHERE a.attstattarget >= 0) AS indstatvals,
             CASE
               WHEN i.indexprs IS NOT NULL THEN
                       pg_catalog.array_agg(a.attname ORDER BY a.attnum)
               ELSE NULL
             END AS indattnames,
             i.indisreplident,
             i.indnkeyatts AS indnkeyatts,
             i.indnatts AS indnatts,
             i.indisvalid,
             i.indnullsnotdistinct
      /* using this rather than the unnest just to get a list of tables */
      FROM (select oid from pg_Class where relkind = 'r') AS src(tbloid)
      INNER JOIN pg_catalog.pg_index i ON (src.tbloid = i.indrelid)
      INNER JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid)
      LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid)
      WHERE (i.indisvalid OR t2.relkind = 'p') AND i.indisready
      GROUP BY i.indexrelid) i
JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid)
LEFT JOIN pg_catalog.pg_tablespace s ON (s.oid = t.reltablespace)
LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid
                                         AND i.indexrelid = c.conindid
                                         AND c.contype IN ('p',
                                                           'u',
                                                           'x'))
LEFT JOIN pg_catalog.pg_inherits inh ON (inh.inhrelid = indexrelid)
ORDER BY i.indrelid,
         indexname;

ignore_system_indexes = on
Execution Time: 53.351 ms
ignore_system_indexes = off
Execution Time: 56.965 ms

-- Standard query
EXPLAIN ANALYZE SELECT t.tableoid,
       t.oid,
       i.indrelid,
       t.relname AS indexname,
       t.relpages,
       t.reltuples,
       t.relallvisible,
       0 AS relallfrozen,
       pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef,
       i.indkey,
       i.indisclustered,
       c.contype,
       c.conname,
       c.condeferrable,
       c.condeferred,
       c.tableoid AS contableoid,
       c.oid AS conoid,
       pg_catalog.pg_get_constraintdef(c.oid, FALSE) AS condef,
       CASE
           WHEN i.indexprs IS NOT NULL THEN
                  (SELECT pg_catalog.array_agg(attname
                                               ORDER BY attnum)
                   FROM pg_catalog.pg_attribute
                   WHERE attrelid = i.indexrelid)
           ELSE NULL
       END AS indattnames,

  (SELECT spcname
   FROM pg_catalog.pg_tablespace s
   WHERE s.oid = t.reltablespace) AS TABLESPACE,
       t.reloptions AS indreloptions,
       i.indisreplident,
       inh.inhparent AS parentidx,
       i.indnkeyatts AS indnkeyatts,
       i.indnatts AS indnatts,

  (SELECT pg_catalog.array_agg(attnum
                               ORDER BY attnum)
   FROM pg_catalog.pg_attribute
   WHERE attrelid = i.indexrelid
     AND attstattarget >= 0) AS indstatcols,

  (SELECT pg_catalog.array_agg(attstattarget
                               ORDER BY attnum)
   FROM pg_catalog.pg_attribute
   WHERE attrelid = i.indexrelid
     AND attstattarget >= 0) AS indstatvals,
       i.indnullsnotdistinct,
       NULL AS conperiod
/* using this rather than the unnest just to get a list of tables */
FROM (select oid from pg_Class where relkind = 'r') AS src(tbloid)
JOIN pg_catalog.pg_index i ON (src.tbloid = i.indrelid)
JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid)
JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid)
LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid
                                         AND i.indexrelid = c.conindid
                                         AND c.contype IN ('p',
                                                           'u',
                                                           'x'))
LEFT JOIN pg_catalog.pg_inherits inh ON (inh.inhrelid = indexrelid)
WHERE (i.indisvalid
       OR t2.relkind = 'p')
  AND i.indisready
ORDER BY i.indrelid,
         indexname;

ignore_system_indexes = on
Execution Time: 6853.262 ms
ignore_system_indexes = off
Execution Time: 66.781 ms