Observe Performance Cookbook: Mark Immutable Resource Columns

Problem

Using a dataset with resources is taking a long time or using a lot of query credits.

Solution

Edit the resource dataset and use the set_col_immutable verb to mark columns that will not change over time. If the primary key field is unique, you should add any time columns to the set_col_immutable list. Verify that the resource use case still works, and save the resource dataset definition.

Explanation

Resources need to review datasets over time. Using set_col_immutable allows the OPAL optimizer to rewrite these queries, as well as other queries that use the resource. For instance, filtering commands are more efficient if more columns are immutable.

Note that primary key columns and label columns are automatically flagged as immutable. It is not necessary to use set_col_immutable on them.

Example

make_resource options(expiry:1h)
  name:name,
  type:type,
  value:value,
  primary_key(id)
// If I know "name" and "type" never changes for the same key "id"
set_col_immutable name:true, type:true

If the primary key is strong, mark time columns as immutable

A strong key means the primary key of an interval or resource is unique in the traditional relational database sense (unique over all rows in the table). Having a strong key for an Interval dataset means Observe knows each interval has a unique key value. In a Resource dataset a unique key value means the resource never changes state. Knowing the strong key property enables powerful compiler optimizations.

Example

// I'm creating a user request interval dataset, and I'm 
// sure that each request has a globally unique ID.
make_session
  options(expiry:10s),
  request_name:any_not_null(name),
  request_tags:any_not_null(tags),
  group_by(request_id)
set_col_immutable @."Valid From":true, @."Valid To":true