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