test_results2.txt

-- setup
---- create table constaraints: 500000
CREATE OR REPLACE FUNCTION create_notnull_table(
    p_table_name text,
    p_col_count  int
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    ddl text;
BEGIN
    SELECT
        format('CREATE TABLE %I (', p_table_name) || E'\n' ||
        string_agg(
            format('  c%s smallint not null', i),
            E',\n'
            ORDER BY i
        ) || E'\n);'
    INTO ddl
    FROM generate_series(1, p_col_count) AS i;

    EXECUTE ddl;
END;
$$;

SELECT create_notnull_table('notnull_temp', 1000);

select 'create table notnull_table_' || i || ' (like notnull_temp including all);'
from generate_series(1, 500) i;
\gexec

drop table notnull_temp;

select count(*)
from   pg_catalog.pg_constraint AS cns2
       JOIN pg_catalog.pg_class c on c.oid = cns2.conrelid
       JOIN pg_catalog.pg_namespace n ON n.oid = cns2.connamespace
where conname like 'notnull%';

---- create domain constraints: 500000
SELECT 'CREATE DOMAIN hoge_domain_'|| i || ' smallint NOT NULL CHECK (VALUE > 0 AND VALUE < 100);'
FROM generate_series(1, 250000) i;
\gexec

-- measurement
---- mine
explain analyze
SELECT n.nspname AS "Schema",
       c.relname AS "Table",
       cns.contype AS "Type",
       cns.conname AS "Name" ,
       pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition"
FROM pg_catalog.pg_constraint cns
     JOIN pg_catalog.pg_namespace n ON n.oid = cns.connamespace
     LEFT JOIN pg_catalog.pg_type t ON t.oid = cns.contypid
     LEFT JOIN pg_catalog.pg_class c on c.oid = cns.conrelid
WHERE ((cns.conrelid <> 0 AND pg_catalog.pg_table_is_visible(cns.conrelid))
      OR (cns.contypid > 0 AND pg_catalog.pg_type_is_visible(t.oid)))
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

---- Alvaro suggested
explain analyze
SELECT n.nspname AS "Schema",
       cns.relname AS "Table",
       cns.contype AS "Type",
       cns.conname AS "Name" ,
       pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition"
FROM ( select cns1.connamespace, NULL as relname, cns1.contype, cns1.conname, cns1.oid 
       from   pg_catalog.pg_constraint AS cns1
              JOIN pg_catalog.pg_type t ON t.oid = cns1.contypid
       where  pg_catalog.pg_type_is_visible(t.oid)
       union all                                         
       select cns2.connamespace, c.relname, cns2.contype, cns2.conname, cns2.oid 
       from   pg_catalog.pg_constraint AS cns2
              JOIN pg_catalog.pg_class c on c.oid = cns2.conrelid
       where  pg_catalog.pg_table_is_visible(cns2.conrelid)
     ) AS cns                         
     JOIN pg_catalog.pg_namespace n ON n.oid = cns.connamespace
WHERE n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4 ;


-- new results
Mine:
 Execution Time: 12080.315 ms
 Execution Time: 12190.169 ms
 Execution Time: 11789.138 ms

Alvaro suggested:
 Execution Time: 12065.964 ms
 Execution Time: 12212.447 ms
 Execution Time: 12319.788 ms

-- compare the cost of the top node
Mine:
 Gather Merge  (cost=80535.98..90890.43 rows=88905 width=224) (actual time=11120.302..11472.557 rows=1000000.00 loops=1)

Alvaro suggested:
 Gather Merge  (cost=126600.94..127378.47 rows=6676 width=225) (actual time=11943.160..12302.426 rows=1000000.00 loops=1)