Six families. All shipping today.
Every construct below runs in production, against the same atomic substrate as vector, graph and full-text — one engine, one write, one bill.
Predicates push down to secondary indexes — the planner splits conjuncts and routes each to the cheapest index.
Left-deep planner across as many as 32 tables. The cap used to be 5; real schemas needed more.
Aggregates wrap any expression, not just bare columns. HAVING composes with OR.
A streaming partition walk on top of the index scan — no spool, no extra storage.
Correlated EXISTS rewrites to a semi-join and rides the same index walker as the outer WHERE.
FK on-delete: NoAction, Restrict, SetNull. CHECK with 3-valued logic. MVs install / refresh / read on demand.
Ask the engine what it did, not what it meant to do.
EXPLAIN ANALYZE returns the plan with
estimated and actual rows per node. When the two drift apart, you've found your
slow query before your users do.
A correlated EXISTS below becomes a semi-join; each side rides its own index. No cursors, no per-row subquery execution.
> EXPLAIN ANALYZE SELECT … WHERE EXISTS (…);
semi_join est 1.1k · actual 1.2k · 3.1 ms
├─ index_scan customers(region) est 1.3k · actual 1.2k
└─ index_scan orders(customer_id) est 41k · actual 40k
point lookup < 5 ms · GROUP BY 10k < 50 ms · EXISTS over 100k < 80 msTwo queries that used to be a "no."
SELECT user_id, amount,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY ts DESC) AS rn
FROM payments;SELECT id, name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
AND o.status = 'open');