Materialize Logo

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 SOURCE or CREATE MATERIALIZED VIEW automatically creates an index which will eagerly materialize that source or view.

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

Conceptual framework

Indexes assemble and maintain in memory a query’s results, which can provide future queries the data they need pre-arranged in a format they can immediately use. In particular, this can be very helpful for the JOIN operator which needs to build and maintain the appropriate indexes if they do not otherwise exist. For more information, see API Components: Indexes.

When to create indexes

You might want to create indexes when…

Syntax

CREATE INDEX index_name ON obj_name ( col_ref , ) DEFAULT INDEX ON obj_name
Field Use
DEFAULT Creates a default index with the same structure as the index automatically created with CREATE MATERIALIZED VIEW or CREATE MATERIALIZED SOURCE. This provides a simple method to convert a non-materialized object to a materialized one.
index_name A name for the index.
obj_name The name of the source or view on 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 indexes will use all columns as key columns for the index, unless Materialize is provided or can infer a unique key for the source or view.

For instance, unique keys can be…

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

Memory footprint

The in-memory sizes of indexes are proportional to the current size of the source or view they represent. The actual amount of memory required depends on several details related to the rate of compaction and the representation of the types of data in the source or view. We are working on a feature to let you see the size each index consumes.

Creating an index may also force the first materialization of a view, which may cause Materialize to install a dataflow to determine and maintain the results of the view. This dataflow may have a memory footprint itself, in addition to that of the index.

Examples

Optimizing joins with indexes

We can optimize the performance of JOIN on two relations by ensuring their join keys are the key 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.)