Timescaledb - Materialized view very slow

Question

Here is my hypertable -

CREATE TABLE  prices_daily (
datetime TIMESTAMP WITH TIME ZONE NOT NULL,
security_id BIGINT  NULL,
open DOUBLE PRECISION NOT NULL,
high DOUBLE PRECISION NOT NULL,
low DOUBLE PRECISION NOT NULL,
close DOUBLE PRECISION NOT NULL,
volume DOUBLE PRECISION NOT NULL,
CONSTRAINT fk_security FOREIGN KEY (security_id) REFERENCES security(id));

SELECT create_hypertable('prices_daily', 'datetime');

create  INDEX idx_prices_daily_sec_dt 
ON prices_daily(datetime, security_id);
ALTER TABLE prices_daily SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'security_id'
);

I have created a continuous aggregation like below -

CREATE MATERIALIZED VIEW prices_monthly
WITH (timescaledb.continuous) AS
SELECT 
    security_id,
    time_bucket('1 month', datetime) AS datetime ,
    first(open, datetime) AS open,
    max(high) AS high,
    min(low) AS low,
    last(close, datetime) AS close,
    sum(volume) AS volume
FROM market.prices_daily 
group by security_id, time_bucket('1 month', datetime)  with DATA;
ALTER MATERIALIZED  VIEW prices_monthly SET (
    timescaledb.materialized_only = false,
    timescaledb.compress = true
);
create  INDEX idx_prices_monthly_dt 
ON prices_monthly(security_id,datetime);

When I run select on the view it took around 2sec for one id. So I ran EXPLAIN and here is the query plan -

EXPLAIN (analyze)
select * from market.prices_monthly  where security_id=1476 and datetime>='1998-01-01' and datetime<='2023-01-24';

    QUERY PLAN                                                                                                                                                                                                                                                     |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append  (cost=0.28..3650.68 rows=1445 width=56) (actual time=0.156..3.860 rows=300 loops=1)                                                                                                                                                                    |
  ->  Custom Scan (ChunkAppend) on _materialized_hypertable_31  (cost=0.28..328.50 rows=131 width=56) (actual time=0.150..3.723 rows=300 loops=1)                                                                                                              |
        Chunks excluded during startup: 0                                                                                                                                                                                                                      |
        ->  Index Scan using _hyper_31_5212_chunk_idx_prices_monthly_dt on _hyper_31_5212_chunk  (cost=0.28..2.51 rows=1 width=56) (actual time=0.142..0.145 rows=2 loops=1)                                                                                   |
              Index Cond: ((security_id = 1476) AND (datetime < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(31)), '-infinity'::timestamp with time zone)) AND (datetime >= '1998-01-01 00:00:00-05'::timestamp with time z|
        ->  Index Scan using _hyper_31_5213_chunk_idx_prices_monthly_dt on _hyper_31_5213_chunk  (cost=0.28..2.51 rows=1 width=56) (actual time=0.019..0.020 rows=3 loops=1)                                                                                   |
              Index Cond: ((security_id = 1476) AND (datetime < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(31)), '-infinity'::timestamp with time zone)) AND (datetime >= '1998-01-01 00:00:00-05'::timestamp with time z|
        ->  Index Scan using _hyper_31_5214_chunk_idx_prices_monthly_dt on _hyper_31_5214_chunk  (cost=0.28..2.51 rows=1 width=56) (actual time=0.020..0.021 rows=2 loops=1)                                                                                   |
              Index Cond: ((security_id = 1476) AND (datetime < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(31)), '-infinity'::timestamp with time zone)) AND (datetime >= '1998-01-01 00:00:00-05'::timestamp with time z|
        ->  Index Scan using _hyper_31_5215_chunk_idx_prices_monthly_dt on _hyper_31_5215_chunk  (cost=0.28..2.51 rows=1 width=56) (actual time=0.023..0.024 rows=2 loops=1)                                                                                   |

    .
    .
    .   

->  GroupAggregate  (cost=3251.23..3300.50 rows=1314 width=56) (actual time=0.028..0.029 rows=0 loops=1)                                                                                                                                                     |
        Group Key: prices_daily.security_id, (time_bucket('1 mon'::interval, prices_daily.datetime))                                                                                                                                                           |
        ->  Sort  (cost=3251.23..3254.51 rows=1314 width=64) (actual time=0.027..0.027 rows=0 loops=1)                                                                                                                                                         |
              Sort Key: (time_bucket('1 mon'::interval, prices_daily.datetime))                                                                                                                                                                                |
              Sort Method: quicksort  Memory: 25kB                                                                                                                                                                                                             |
              ->  Custom Scan (ChunkAppend) on prices_daily  (cost=0.29..3183.16 rows=1314 width=64) (actual time=0.000..0.001 rows=0 loops=1)                                                                                                                 |
                    Chunks excluded during startup: 1314                                                                                                                                                                                                       |
Planning Time: 1601.287 ms                                                                                                                                                                                                                                     |
Execution Time: 59.823 ms                                                                                                                                                                                                                                      |

Here is the data size : prices_daily = 5.5Million rows, prices_monthly = ~254K rows
My machines info - RAM - 4GB vCPUs - 2, postgresql version - 14.7, timescaledb - 2.9.3

I tried prepared statements and parametrized queries to see if the planning time will reduce but no use. I use this for mostly read operations so I tried updating postgresql.conf with recommended settings -

max_connections = 20
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 1GB
max_wal_size = 4GB

Please suggest how can I improve query performance

score 2 · Answer 1 · answered Feb 26 '23 at 12:42

I have resolved it by setting chunk_time_interval. It seems there are underlying tables created for materialized views, so we have set the chunk interval for those tables. Here is the query to find out the underlying hypertables for the views -

SELECT view_name, *    FROM timescaledb_information.continuous_aggregates;

Here is how I set chunk_time_interval for those underlying tables

 SELECT set_chunk_time_interval('_timescaledb_internal._materialized_hypertable_17', INTERVAL '1 year');

Now the planning and execution time reduced to ~40ms.

Timescaledb - Materialized view very slow

1 Answers1