Idiomatic Materialize SQL

Materialize follows the SQL standard (SQL-92) implementation and strives for compatibility with the PostgreSQL dialect. However, for some use cases, Materialize provides its own idiomatic query patterns that can provide better performance.

Window functions

Window Function Idiomatic Materialize
First value within groups Use MIN/MAX ... GROUP BY subquery.
Lag over a regularly increasing field Use self join or a self LEFT JOIN/LEFT OUTER JOIN by an equality match on the regularly increasing field.
Last value within groups Use MIN/MAX ... GROUP BY subquery
Lead over a regularly increasing field Use self join or a self LEFT JOIN/LEFT OUTER JOIN by an equality match on the regularly increasing field.
Top-K Use an ORDER BY ... LIMIT subquery with a LATERAL JOIN on a DISTINCT subquery (or, for K=1, a SELECT DISTINCT ON ... ORDER BY ... LIMIT query)

General query patterns

Query Pattern Idiomatic Materialize
ANY() Equi-join condition Use UNNEST() or DISTINCT UNNEST() to expand the values and join.
mz_now() with date/time operators Move the operation to the other side of the comparison:
mz_now() with disjunctions (OR) in materialized/indexed view definitions and SUBSCRIBE statements: Rewrite using UNION ALL or UNION (deduplicating as necessary) expression
Back to top ↑