SQL, read not run

Production SQL from real BI work, anonymized and published to be read. Every script here once fed a Power BI table from a live database. The names are gone; the logic, the structure and every optimization are exactly what runs in production.

4 min 54 s

PO view, before

15 s

PO view, after

Same 3,600 rows, checked one to one. No index added, no memory or cache setting touched. Only the query changed.

Catalog

PO_VIEW.sql

PostgreSQL 16Citus2,288 lines29 tables

Purchase-order tracking view: PO lines matched to shipments, lead times and SLA exceptions, customs, invoices. One row per PO line per shipment, with the remaining quantity as its own line until it reaches zero.

More scripts land here as they are anonymized. Each gets a folder with the script, a NOTES.md on what changed and why, and the before and after numbers.

The deploy pattern

Each file is self-contained: the query goes into a session variable with dollar quoting, then into a sql_source table keyed by report and page. The semantic model reads one row. Editing the file changes nothing on its own: edit, run, refresh, commit.

set dev.po_view =
$sql$
    ...the query...
$sql$;

update sql_source
set  _code    = current_setting ( 'dev.po_view' )
    ,_updated = now()
where 1=1
    and _report = 'TRACKER'
    and _page   = 'PO VIEW';

How the PO view got 19x faster

Change	Before	After
Costs	aggregated for every shipment in every domain, 6.6M rows, 2.4 GB	only the shipments in scope
Shipment JSON	decompressed about 250 times per row, 286 separate reads	parsed once into a CTE, then hash joins
Port names	port table scanned once per output row, twice	one join
Shipments, tracker view	read twice	read once, relevant serials only
SLA map	built once per row	built once per company

The biggest single win was not the big tables. Two per-row lookups on a small port table took 73 percent of the old run time and moved hundreds of gigabytes through the buffer cache. The planner expected 14 trillion rows at one window step; 1,600 came out.

House style

Keywords lowercase. One column per line, leading commas.
Every output column gets a _ alias, no as, right-aligned into one block.
One-letter table aliases. where 1=1, then one and per line, so any filter can be toggled.
case / when / then stepped one indent per level.

What anonymized means here

Company, system, client and report identifiers renamed consistently across table names, column names, literals and comments.
Site names, airport and partner codes, debug serials replaced with placeholders.
Nothing else changed. None of the scripts will run outside its source database, and that is the point.