Greek Myth Data: Timing CTEs vs Plain SQL

I’ve been working on a personal sql project like a monk scribbling on a scroll: 120k deities, 150k heroes, 1.5M quests, 2M omens, plus hundreds of thousands of battle logs. Everything ships in a Docker image so you can spin up mythic-cte, run the bundled benchmark_queries.sql, and immediately compare Common Table Expressions against equivalent “plain” SQL.

The benchmark script suppresses row output and only prints labels and timings. Here’s what my last run reported:

CTE - Omen pressure analytics:     258 ms
Non-CTE - Omen pressure analytics: 178 ms

CTE - Hero quest performance:      1.80 s
Non-CTE - Hero quest performance:  1.64 s

CTE - Pantheon lineage depth ≥ 8:  274 ms
Non-CTE - Lineage via self joins:   30 ms

Those numbers need context, so let’s walk through each pair that lives in benchmark_queries.sql.

1. Omen pressure analytics

Both versions calculate “ominous pressure” by slicing the omens table down to the last 14 days, grouping per region, and joining back to the pantheon. The CTE flavor defines

WITH recent AS (...),
     region_density AS (...),
     deity_region AS (...)
SELECT ...

so the filtered set is materialized once and reused. The non-CTE variant repeats the same WHERE clause in two separate subqueries. On a cold cache the CTE wins because it only reads two million omen rows once. In the timing above my cache was already warm, so the repeated scans didn’t cost much and the inline version squeaked ahead. When you add EXPLAIN (ANALYZE, BUFFERS) you’ll see the non-CTE query performs roughly twice as many shared buffer hits even when wall time is similar, which matters once the data no longer fits in RAM.

2. Hero quest performance

This benchmark takes the 1.5M-row quests table plus battle_logs and answers, “How often does each hero succeed, how hard are their assignments, and how many battles have they seen lately?” The CTE version gives each aggregation a name:

WITH hero_effort AS (...),
     hero_success AS (...),
     recent_battles AS (...)
SELECT ...

The non-CTE version embeds the same three aggregations inline, which means the planner can’t reuse the scan results. Again, with warm caches the “plain” query looked faster (1.64s vs 1.80s), but the CTE plan avoids redundant work when the tables are colder and, more importantly, reads like documentation. Tuning these queries is much easier when each step has a name you can isolate.

3. Pantheon lineage

This is the only truly recursive workload. The CTE walks the entire deity tree, starting at Chaos and descending until depth ≥ 8. The “non-CTE” comparison fakes it with six left joins:

SELECT ...
FROM deities d0
LEFT JOIN deities d1 ON d1.parent_id = d0.id
...
LEFT JOIN deities d6 ON d6.parent_id = d5.id

Of course the self-join version is faster—it only sees six generations. The recursive CTE costs 274 ms but explores arbitrarily deep chains, which is something the fixed-depth query can’t even express. Bump the requested depth to 20 and the self-join collapses; the recursive version keeps working.

Takeaways

• CTEs aren’t mythic, but they’re great for sharing filtered subsets, documenting intent, and unlocking recursion. When you benchmark both sides, make sure you’re asking the same question—our lineage example proves that the fastest query can also be the wrong one.
• Warm caches hide inefficiencies. Fire up EXPLAIN (ANALYZE, BUFFERS) and compare block hits; you’ll notice the non-CTE variants doing twice the I/O even when the stopwatch looks close.
• Readable SQL pays for itself. The named CTE blocks in benchmark_queries.sql make step-by-step telemetry easier, which matters once a statement grows beyond a single screen.

Reproduce it yourself

docker build -t mythic-cte .
docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=ambrosia mythic-cte
psql postgresql://demigod:ambrosia@localhost:5432/db -f benchmark_queries.sql

Here is the project link again. The script prints only the label and Time: line, so you can paste results straight into slides or blog posts. Tweak the LIMITs, change the time windows, or wrap individual blocks with EXPLAIN (ANALYZE, BUFFERS) to see what Postgres is really doing under the hood. The point isn’t that CTEs are always faster—it’s that on a dataset big enough to be interesting, you can finally see when they help and why.