Lean and fast Postgres DB introspection

introspect.sql

--
-- I was using https://gist.github.com/jarnaldich/d5952a134d89dfac48d034ed141e86c5 but (I think) some of the
-- joins in there are quite slow; I could probably integrate what I've learned here back into there but this
-- one is good enough for what I'm currently doing
--
-- The MATERIALIZED CTEs seemed to be what fixed the performance- I think without it all joins on .constraint_name
-- (which I guess doesn't have an index) were being done sequentially, each time

WITH
    tc AS MATERIALIZED (
        SELECT
            tc.*
        FROM
            information_schema.table_constraints tc
        WHERE
            tc.constraint_type = 'FOREIGN KEY'
    ),
    kcu AS MATERIALIZED (
        SELECT
            kcu.*
        FROM
            information_schema.key_column_usage kcu
            INNER JOIN tc ON tc.constraint_name = kcu.constraint_name
    ),
    ccu AS MATERIALIZED (
        SELECT
            ccu.*
        FROM
            information_schema.constraint_column_usage ccu
            INNER JOIN tc ON tc.constraint_name = ccu.constraint_name
    ),
    foreign_keys AS (
        SELECT
            tc.table_catalog,
            tc.table_schema,
            kcu.table_name,
            kcu.column_name,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name
        FROM
            tc
            INNER JOIN kcu ON kcu.constraint_name = tc.constraint_name
            INNER JOIN ccu ON ccu.constraint_name = tc.constraint_name
    ),
    columns AS (
        SELECT
            c.table_catalog,
            c.table_schema,
            c.table_name,
            c.column_name,
            c.data_type,
            fk.foreign_table_name,
            fk.foreign_column_name
        FROM
            information_schema.columns c
            LEFT JOIN foreign_keys fk ON c.table_catalog = fk.table_catalog
            AND c.table_schema = fk.table_schema
            AND c.table_name = fk.table_name
            AND c.column_name = fk.column_name
        ORDER BY
            c.table_catalog,
            c.table_schema,
            c.table_name,
            c.column_name ASC
    )
SELECT
    c.*
FROM
    columns c