Loading Grafana dashboard takes too long time

Grafana PostgreSQL
5

Actually, when a continuous aggregate is created in Timescale, an index is automatically created for each GROUP BY column. The index is a composite index, combining the GROUP BY column with the time_bucket column.

Here is the proof:

testdb=> \d+ _timescaledb_internal._materialized_hypertable_205
                                  Table "_timescaledb_internal._materialized_hypertable_205"
   Column    |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 mtimestamp  | timestamp with time zone |           | not null |         | plain   |             |              | 
 apartmentid | integer                  |           |          |         | plain   |             |              | 
 roomid      | integer                  |           |          |         | plain   |             |              | 
 sensorid    | integer                  |           |          |         | plain   |             |              | 
 metric      | smallint                 |           |          |         | plain   |             |              | 
 mvalue      | numeric                  |           |          |         | main    |             |              | 
Indexes:
    "_materialized_hypertable_205_apartmentid_mtimestamp_idx" btree (apartmentid, mtimestamp DESC)
    "_materialized_hypertable_205_metric_mtimestamp_idx" btree (metric, mtimestamp DESC)
    "_materialized_hypertable_205_mtimestamp_idx" btree (mtimestamp DESC)
    "_materialized_hypertable_205_roomid_mtimestamp_idx" btree (roomid, mtimestamp DESC)
    "_materialized_hypertable_205_sensorid_mtimestamp_idx" btree (sensorid, mtimestamp DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON _timescaledb_internal._materialized_hypertable_205 FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Access method: heap
1 Like