perf: optimise /tables route

[kiwicopple]

It's proving extremely slow in prod. I assume the with joins are running sequentially and will need to be converted to regular joins

[kiwicopple]

Running tests, second pass:

running perf tests on the code:

[kiwicopple]

The quickest win that we can do here is to convert all the CTE's to materialized CTE's:

As we can see this has reduced the query by roughly a factor of 5. The caveat here is that the MATERIALIZED keyword for CTE's was only introduced in PG12 (and I don't think there is backwards compatibility), so we may have to look into alternative methods if we want to support older Postgres versions.

Lastly, our queries in general can probably be better. We are optimizing for development speed here, but in the future we may want to do a thorough optimization

[github-actions]

🎉 This issue has been resolved in version 0.10.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀

[soedirgo]

I think this is mostly because we are JOINing information_schema views with system tables, e.g.:

-- tables.sql
...
FROM
  information_schema.tables
  JOIN pg_class c ON quote_ident(table_schema)::regnamespace = c.relnamespace
  AND c.relname = table_name
  LEFT JOIN pg_stat_user_tables ON pg_stat_user_tables.schemaname = tables.table_schema
  AND pg_stat_user_tables.relname = tables.table_name
...

information_schema views already use system tables under the hood (like below), so we could inline it, though it'd make the SQL templates pretty ugly.

-- information_schema.tables
...
FROM pg_namespace nc
  JOIN pg_class c ON nc.oid = c.relnamespace
  LEFT JOIN (pg_type t
  JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
...

The performance difference is big though (10x):

# Not inlined
❯ hyperfine 'curl -s http://localhost:1337/tables' --warmup 5
Benchmark #1: curl -s http://localhost:1337/tables
  Time (mean ± σ):      1.004 s ±  0.027 s    [User: 4.3 ms, System: 5.5 ms]
  Range (min … max):    0.983 s …  1.063 s    10 runs

# Inlined
❯ hyperfine 'curl -s http://localhost:1337/tables' --warmup 5
Benchmark #1: curl -s http://localhost:1337/tables
  Time (mean ± σ):      97.6 ms ±   5.3 ms    [User: 4.2 ms, System: 5.1 ms]
  Range (min … max):    91.9 ms … 110.0 ms    27 runs

[kiwicopple]

that's a massive difference @soedirgo - great find. Let's definitely do that - I'll start a new issue