CREATE INDEX

WARNING! This is an advanced feature of Materialized; most users will not need to manually create indexes to maximize the value Materialize offers, as running CREATE MATERIALIZED VIEW automatically creates all required indexes to eagerly materialize that view.

CREATE INDEX creates an in-memory index on a view.

Conceptual framework 

Indexes persist some subset of a query’s data to memory, which let materialized views quickly perform reads from materialized views, which includes performing JOINs. For more information, see API Components: Indexes.

When to create indexes 

You might want to create indexes when…

Syntax 

CREATE INDEX index_name ON view_name ( col_ref , )
Field Use
index_name A name for the index.
view_name The name of the view for which you want to create an index.
col_ref The columns to use as the key into the index.

Details 

Restrictions 

Structure 

Indexes in Materialize have the following structure for each unique row.

((tuple of indexed columns), (tuple of the row, i.e. stored columns))

Indexed columns vs. stored columns 

Automatically created columns index all of a table’s columns, unless Materialize is provided or can infer a unique key for the result set.

For instance, unique keys can be…

When creating your own indexes, you can choose the indexed columns.

Memory footprint 

We do not currently have a good “rule of thumb” for understanding the size of indexes and are working on a feature to let you see the size each index consumes.

Examples 

Optimizing joins with indexes 

We can optimize the performance of JOIN on two relations by ensuring their join keys are the leading columns in an index.

CREATE MATERIALIZED VIEW active_customers AS
    SELECT
        guid, geo_id, last_active_on
    FROM
        customer_source
    WHERE
        last_active_on > now() - INTERVAL '30' DAYS;

CREATE INDEX active_customers_geo_idx
    ON active_customers (geo_id);

CREATE MATERIALIZED VIEW active_customer_per_geo AS
    SELECT
        geo.name, count(*)
    FROM
        geo_regions AS geo
        JOIN active_customers ON
                active_customers.geo_id = geo.id
    GROUP BY
        geo.name;

In the above example, the index active_customers_geo_idx

Materializing views 

You can convert a non-materialized view into a materialized view by adding an index to it.

CREATE VIEW active_customers AS
    SELECT
        guid, geo_id, last_active_on
    FROM
        customer_source
    WHERE
        last_active_on > now() - INTERVAL '30' DAYS;

CREATE INDEX active_customers_primary_idx
    ON active_customers (guid);

Note that this index is different than the primary index that Materialize would automatically create if you had used CREATE MATERIALIZED VIEW. Indexes that are automatically created contain an index of all columns in the result set, unless they contain a unique key. (Remember that indexes store a copy of a row’s indexed columns and a copy of the entire row.)