test_results.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",
       cns.conname AS "Name" ,
       pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition",
       c.relname AS "Table"
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 n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND (
        (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))
  )
ORDER BY 1, 2, 4;

---- Alvaro suggested
explain analyze
SELECT n.nspname AS "Schema",
       cns.conname AS "Name" ,
       pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition",
       cns.relname AS "Table"
FROM ( select cns1.*, NULL as relname 
       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.*, c.relname 
       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'
  AND n.nspname like 'many_cns'
ORDER BY 1, 2, 4 ;

-- results
Mine:
 Execution Time: 9079.703 ms
 Execution Time: 8974.628 ms
 Execution Time: 9092.482 ms

Alvaro suggested:
 Execution Time: 4.251 ms
 Execution Time: 3.744 ms
 Execution Time: 3.887 ms