Materialized view slow with Timescale

Question

I have a table created like this:

-- enable timescale plugin
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- create schema
CREATE SCHEMA IF NOT EXISTS {schemaName};

-- create table
CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
    ts           TIMESTAMP    NOT NULL,
    ticker       VARCHAR(16)  NOT NULL,
    m1           FLOAT4       NOT NULL,
    m5           FLOAT4       NOT NULL,
    m15          FLOAT4       NOT NULL,
    m30          FLOAT4       NOT NULL,
    h1           FLOAT4       NOT NULL,
    h2           FLOAT4       NOT NULL,
    h4           FLOAT4       NOT NULL,
    d1           FLOAT4       NOT NULL,
    high         FLOAT4       NOT NULL,
    vwap         FLOAT4       NOT NULL,
    low          FLOAT4       NOT NULL
);

-- create hypertable
SELECT create_hypertable('{schemaName}.{tableName}', 'ts', create_default_indexes => false, chunk_time_interval => INTERVAL '1 day', if_not_exists => TRUE);

-- set autovacum
ALTER TABLE {schemaName}.{tableName} SET (autovacuum_enabled = on);

-- create index
CREATE INDEX IF NOT EXISTS idx_kvwap_ticker_ts ON {schemaName}.{tableName}(ticker, ts DESC);

The data has one row per minute, but sometimes we need a large time span, for example one year. So I created views that do an aggregation of the data for different timeframes so we pull fewer data from the server.

The table has roughly 1.5M rows per ticker and about 140 tickers, for a total of about ~210M rows.

I create aggregate views like this:

let viewsCreationSQL =
    let intervals = [| oneDay; fourHours; twoHours; oneHour; thirtyMinutes; fifteenMinutes; fiveMinutes; oneMinute |]

    stringBuffer {
        for i in intervals do
            yield
                $"
                \n
                CREATE MATERIALIZED VIEW IF NOT EXISTS {schemaName}.kvwap_{i.ShortString.ToLower()}
                WITH (timescaledb.continuous) AS
                (
                    SELECT
                        time_bucket(INTERVAL '{i.TotalSeconds} second', ts) AS ts_bucket,
                        min(ticker)                  AS ticker,
                        round(avg(m1)::decimal,  8)  AS m1,
                        round(avg(m5)::decimal,  8)  AS m5,
                        round(avg(m15)::decimal, 8)  AS m15,
                        round(avg(m30)::decimal, 8)  AS m30,
                        round(avg(h1)::decimal,  8)  AS h1,
                        round(avg(h2)::decimal,  8)  AS h2,
                        round(avg(h4)::decimal,  8)  AS h4,
                        round(avg(d1)::decimal,  8)  AS d1,
                        min(low)                     AS low,
                        round(avg(vwap)::decimal, 8) AS vwap,
                        max(high)                    AS high
                    FROM
                        {schemaName}.kvwap
                    GROUP BY ticker, ts_bucket
                    ORDER BY ts_bucket
                );

                CREATE INDEX IF NOT EXISTS idx_kvwap_ticker_ts_{i.ShortString.ToLower()}
                ON {schemaName}.kvwap_{i.ShortString.ToLower()} (ticker, ts_bucket);
                "

    }

The views work as expected, but they are very slow. It takes about 2m to get the top 500 rows and will use a full CPU core during that time.

I thought the Timescale materialized views were creating tables behind the scenes, but it doesn't seem to be the case considering how slow the access is. Right now, pulling all the rows needed and aggregating them in the client performs similarly.

What am I missing?

---

edit:

I'm using latest-pg14 (docker image)

The command used is from the DataGrip viewer:

SELECT t.* FROM exchange_debug.kvwap_d1 t LIMIT 501

It really looks like the materialized view is being rebuilt for every query.

I can see that Timescale added triggers to the table:

create table if not exists exchange_debug.kvwap
(
    ts     timestamp   not null,
    ticker varchar(16) not null,
    m1     real        not null,
    m5     real        not null,
    m15    real        not null,
    m30    real        not null,
    h1     real        not null,
    h2     real        not null,
    h4     real        not null,
    d1     real        not null,
    high   real        not null,
    vwap   real        not null,
    low    real        not null
)
    with (autovacuum_enabled = on);

alter table exchange_debug.kvwap
    owner to root;

create index if not exists idx_kvwap_ticker_ts
    on exchange_debug.kvwap (ticker asc, ts desc);

create trigger ts_insert_blocker
    before insert
    on exchange_debug.kvwap
    for each row
execute procedure ???();

create trigger ts_cagg_invalidation_trigger
    after insert or update or delete
    on exchange_debug.kvwap
    for each row
execute procedure ???('115');