Create a composite index

CREATE INDEX idx_customer_date
ON TABLE sales.orders (
    customer_id,
    order_date
)
USING pgm
WITH (auto_update = true);
PGM is the default row-index algorithm. Declaring it makes the choice explicit.

Queries that use it

The index can serve the first column alone or both columns together:

SELECT *
FROM sales.orders
WHERE customer_id = 1042;

SELECT *
FROM sales.orders
WHERE customer_id = 1042
  AND order_date BETWEEN DATE '2026-06-01'
                     AND DATE '2026-06-15';
Equality on the leading column narrows the composite key before the date range is applied.

A query that does not use it

SELECT *
FROM sales.orders
WHERE order_date = DATE '2026-06-15';
This skips customer_id, so it cannot use the leftmost prefix of the index.

Why PGM is different

A B+ tree stores separator keys through a tree. PGM learns compact piecewise functions over the sorted keys, predicts a position, and performs an exact search around that prediction. It is usually the smaller general-purpose choice, while B+ tree offers more distribution-independent behavior.

Choose the column order from the workload

  • Put the column used by most selective queries first.
  • Add a second column when queries commonly filter both together.
  • Create a separate index for frequent queries that start from another column.

Keep it current

With auto_update = true, parent writes maintain the index. Without it, rebuild after data changes:

REBUILD INDEX idx_customer_date
ON TABLE sales.orders;
If an index is not current for the table snapshot, DeltaForge uses the normal query path.

When it fits

Use a composite PGM index for selective customer-and-date lookups, tenant-scoped event ranges, device-and-time queries, or other workloads with a stable leading key. Broad analytical scans should continue to use Delta Lake statistics and data skipping.