Slow query for multiple data at same timestamp

Question

Hi everyone,
I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp.
I will explain,

I have two table of data using TimescaleDb Hypertable with following schema :

CREATE TABLE raw_data (
  timestamp TIMESTAMPTZ  NOT NULL,
  value double precision,
  variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE,
  PRIMARY KEY (timestamp, variable_id, value)
);
SELECT create_hypertable('raw_data', 'timestamp');

CREATE TABLE measures (
  timestamp TIMESTAMPTZ NOT NULL,
  value double precision,
  variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE,
  PRIMARY KEY (timestamp, variable_id, value)
);
SELECT create_hypertable('measures', 'timestamp');

Database size:

raw_data : 106 524 480 rows
measures : 24 942 692 rows

Database Index:

raw data:
- raw_data_pk : (timestamp, value, variable_id)
- raw_data_variable_id: (timestamp, variable_id)
measures
- measures_pk : (timestamp, value, variable_id)
- measure_variable_id: (timestamp, variable_id)

I have a query that have 3 parameters :

variablesId ( list of integer)
limit (number of timestamp to retrieve )
lastDate ( Date )
timeZone ( time where we want to retrieve data )

the goal is to retrieve each synchronised timestamp for all list of timestamp like this :

  [{
    timestamp: xxxxx, data: [
      { variable_id: yy1, value: zz1 },
      { variable_id: yy2, value: zz1 }],
    etc.. 
  }]

Today i use following query:

const queryText = {
    text: ` 
      select json_agg(joined.measures) as measures , joined.x from (
        select (rd.value,rd.variable_id) as measures , timestamp at time zone $4 as x
        from raw_data rd
        inner join variables v on rd.variable_id = v.id
        where rd.variable_id  =  ANY ($1) and ($2::text is null or rd.timestamp<=to_timestamp($2,'YYYY-MM-DD HH24:MI:SS'))
        union all
        select (m.value,m.variable_id) as measures , timestamp at time zone $4  as x
        from measures m
        inner join variables v on m.variable_id = v.id
        where m.variable_id  =  ANY ($1)  and ($2::text is null or m.timestamp<=to_timestamp($2,'YYYY-MM-DD HH24:MI:SS'))
      ) as joined
      group by joined.x
      order by joined.x desc
      limit $3`,
    values: [variablesId, lastDate, number, currentTimezone],
  };

Which gave me following explain_analyse:

Limit  (cost=1264677.58..1264677.62 rows=15 width=40) (actual time=12270.974..12300.678 rows=15 loops=1)
  ->  Sort  (cost=1264677.58..1264678.08 rows=200 width=40) (actual time=3399.579..3429.281 rows=15 loops=1)
        Sort Key: (timezone('Europe/Paris'::text, rd."timestamp")) DESC
        Sort Method: top-N heapsort  Memory: 71kB
        ->  HashAggregate  (cost=1264670.18..1264672.68 rows=200 width=40) (actual time=3399.246..3429.119 rows=350 loops=1)
              Group Key: (timezone('Europe/Paris'::text, rd."timestamp"))
              ->  Append  (cost=884.46..1258372.25 rows=1259586 width=40) (actual time=17.406..3417.027 rows=10879 loops=1)
                    ->  Hash Join  (cost=884.46..449576.89 rows=759442 width=40) (actual time=17.404..31.224 rows=10879 loops=1)
                          Hash Cond: (rd.variable_id = v.id)
                          ->  Append  (cost=0.27..444800.17 rows=759442 width=20) (actual time=12.035..17.309 rows=10879 loops=1)
                                ->  Index Scan using _hyper_19_6990_chunk__index_raw_variable_id on _hyper_19_6990_chunk rd  (cost=0.27..15.29 rows=1 width=20) (actual time=0.043..0.043 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6991_chunk__index_raw_variable_id on _hyper_19_6991_chunk rd_1  (cost=0.28..22.04 rows=1 width=20) (actual time=0.029..0.029 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6992_chunk__index_raw_variable_id on _hyper_19_6992_chunk rd_2  (cost=0.28..22.04 rows=1 width=20) (actual time=0.028..0.029 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6993_chunk__index_raw_variable_id on _hyper_19_6993_chunk rd_3  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6994_chunk__index_raw_variable_id on _hyper_19_6994_chunk rd_4  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6995_chunk__index_raw_variable_id on _hyper_19_6995_chunk rd_5  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6996_chunk__index_raw_variable_id on _hyper_19_6996_chunk rd_6  (cost=0.28..22.12 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Seq Scan on _hyper_19_6997_chunk rd_7  (cost=0.00..4.51 rows=1 width=20) (actual time=0.054..0.054 rows=0 loops=1)
                                      Filter: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                      Rows Removed by Filter: 72
                                ->  Index Scan using _hyper_19_6998_chunk__index_raw_variable_id on _hyper_19_6998_chunk rd_8  (cost=0.14..8.03 rows=1 width=20) (actual time=0.019..0.019 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_6999_chunk__index_raw_variable_id on _hyper_19_6999_chunk rd_9  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.028 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7000_chunk__index_raw_variable_id on _hyper_19_7000_chunk rd_10  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7001_chunk__index_raw_variable_id on _hyper_19_7001_chunk rd_11  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7002_chunk__index_raw_variable_id on _hyper_19_7002_chunk rd_12  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7003_chunk__index_raw_variable_id on _hyper_19_7003_chunk rd_13  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7004_chunk__index_raw_variable_id on _hyper_19_7004_chunk rd_14  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7005_chunk__index_raw_variable_id on _hyper_19_7005_chunk rd_15  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7006_chunk__index_raw_variable_id on _hyper_19_7006_chunk rd_16  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7007_chunk__index_raw_variable_id on _hyper_19_7007_chunk rd_17  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7008_chunk__index_raw_variable_id on _hyper_19_7008_chunk rd_18  (cost=0.28..22.04 rows=1 width=20) (actual time=0.028..0.028 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7009_chunk__index_raw_variable_id on _hyper_19_7009_chunk rd_19  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7010_chunk__index_raw_variable_id on _hyper_19_7010_chunk rd_20  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7011_chunk__index_raw_variable_id on _hyper_19_7011_chunk rd_21  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7012_chunk__index_raw_variable_id on _hyper_19_7012_chunk rd_22  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7013_chunk__index_raw_variable_id on _hyper_19_7013_chunk rd_23  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7014_chunk__index_raw_variable_id on _hyper_19_7014_chunk rd_24  (cost=0.28..22.04 rows=1 width=20) (actual time=0.025..0.025 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7015_chunk__index_raw_variable_id on _hyper_19_7015_chunk rd_25  (cost=0.28..22.04 rows=1 width=20) (actual time=0.025..0.025 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7016_chunk__index_raw_variable_id on _hyper_19_7016_chunk rd_26  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7017_chunk__index_raw_variable_id on _hyper_19_7017_chunk rd_27  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7018_chunk__index_raw_variable_id on _hyper_19_7018_chunk rd_28  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7019_chunk__index_raw_variable_id on _hyper_19_7019_chunk rd_29  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7020_chunk__index_raw_variable_id on _hyper_19_7020_chunk rd_30  (cost=0.28..22.04 rows=1 width=20) (actual time=0.025..0.025 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7021_chunk__index_raw_variable_id on _hyper_19_7021_chunk rd_31  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7022_chunk__index_raw_variable_id on _hyper_19_7022_chunk rd_32  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7023_chunk__index_raw_variable_id on _hyper_19_7023_chunk rd_33  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7024_chunk__index_raw_variable_id on _hyper_19_7024_chunk rd_34  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7025_chunk__index_raw_variable_id on _hyper_19_7025_chunk rd_35  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7026_chunk__index_raw_variable_id on _hyper_19_7026_chunk rd_36  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7027_chunk__index_raw_variable_id on _hyper_19_7027_chunk rd_37  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7028_chunk__index_raw_variable_id on _hyper_19_7028_chunk rd_38  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7029_chunk__index_raw_variable_id on _hyper_19_7029_chunk rd_39  (cost=0.28..24.32 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7030_chunk__index_raw_variable_id on _hyper_19_7030_chunk rd_40  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7031_chunk__index_raw_variable_id on _hyper_19_7031_chunk rd_41  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7032_chunk__index_raw_variable_id on _hyper_19_7032_chunk rd_42  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7033_chunk__index_raw_variable_id on _hyper_19_7033_chunk rd_43  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7034_chunk__index_raw_variable_id on _hyper_19_7034_chunk rd_44  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7035_chunk__index_raw_variable_id on _hyper_19_7035_chunk rd_45  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7036_chunk__index_raw_variable_id on _hyper_19_7036_chunk rd_46  (cost=0.28..22.04 rows=1 width=20) (actual time=0.027..0.027 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7037_chunk__index_raw_variable_id on _hyper_19_7037_chunk rd_47  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7038_chunk__index_raw_variable_id on _hyper_19_7038_chunk rd_48  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7039_chunk__index_raw_variable_id on _hyper_19_7039_chunk rd_49  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7040_chunk__index_raw_variable_id on _hyper_19_7040_chunk rd_50  (cost=0.28..22.04 rows=1 width=20) (actual time=0.025..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7041_chunk__index_raw_variable_id on _hyper_19_7041_chunk rd_51  (cost=0.28..22.04 rows=1 width=20) (actual time=0.025..0.025 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7042_chunk__index_raw_variable_id on _hyper_19_7042_chunk rd_52  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                ->  Index Scan using _hyper_19_7043_chunk__index_raw_variable_id on _hyper_19_7043_chunk rd_53  (cost=0.28..22.04 rows=1 width=20) (actual time=0.026..0.026 rows=0 loops=1)
                                      Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))

But this query is very slow, i try to find some point of optimisation:

Query all variables separatly ? Do aggregration server side ?
Remove json_agg which is slow ? but i can not apply limit cause i don't know number of rows to select to have same timestamp for each variable
Should i remove union on both tables ?
Shoud i set timezone before to remove at time zone ?

Today the query for 10 variables and a limit of 10 is about 15seconds which is very slow for users.

I am open to any advice, any test. thank you in advance Have a Good Day.

Your plan doesn't seem to be valid. Did it get truncated in posting? — jjanes, Commented Mar 14, 2022 at 21:29
How many rows are in those tables per variable_id? You’d probably find it faster if you reorder your %variable_id indexes so that they lead with variable_id rather than timestamp — Andrew Sayer, Commented Mar 14, 2022 at 22:09
yes the plan is truncated due to limit number of row in StackExchange — Eventeos, Commented Mar 16, 2022 at 10:14
Could you find some external site that lets you post the whole thing? Like explain.depesz.com. Alternatively, maybe you can truncate it strategically. We don't need to see every single partition feeding the appends, but we should see every APPEND. Right now entire branches have been lopped off. — jjanes, Commented Mar 17, 2022 at 0:27
Are you using the "Distributed hypertables" feature to spread the work over multiple nodes? The huge time gap between the end of the Sort and the start of the Limit is completely baffling to me and I wonder if that is some kind of network drop out of distributed computing. Also is this reproducible? — jjanes, Commented Mar 17, 2022 at 0:34

greenweeds · Accepted Answer · 2022-03-16 11:35:57Z

I think the issue is likely to be that your keys are ordered like this

timestamp, variable_id, value

But your query select conditions are leading on variable_id

This means that the plan can't use the index because the index sort is on timestamp first and then variable_id

If you switch those around so that the key is ordered as variable_id, timestamp, value (or even variable_id, value, timestamp) and do the explain again you should see that the plan can now use variable_id from the index.

If you google for multi-column indexes in PostgreSQL that should help explain the reasoning. If it's a production database please try it on a test or development environment first! There may be other parts of the application that assume timestamp is the lead column.

Eventeos · Accepted Answer · 2022-03-22 13:20:11Z

Following @greenweeds answer i have changed my schema as follow :

CREATE TABLE raw_data (
  timestamp TIMESTAMPTZ  NOT NULL,
  value double precision,
  last_data BOOLEAN default true,
  variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (variable_id,timestamp)
);
SELECT create_hypertable('raw_data', 'timestamp');

CREATE TABLE measures (
  timestamp TIMESTAMPTZ NOT NULL,
  value double precision,
    variable_id INTEGER REFERENCES variables(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (variable_id, timestamp)
);
SELECT create_hypertable('measures', 'timestamp');

So i changed the order of my primary key and then of my index. Was successful i gain 10 seconds on my query.

Here is the new lite version of the query plan :

Limit  (cost=736888.88..736888.92 rows=15 width=40) (actual time=29087.058..29087.261 rows=15 loops=1)
  ->  Sort  (cost=736888.88..736889.38 rows=200 width=40) (actual time=193.875..194.075 rows=15 loops=1)
        Sort Key: (timezone('Europe/Paris'::text, rd."timestamp")) DESC
        Sort Method: top-N heapsort  Memory: 71kB
        ->  HashAggregate  (cost=736881.48..736883.98 rows=200 width=40) (actual time=193.352..193.781 rows=385 loops=1)
              Group Key: (timezone('Europe/Paris'::text, rd."timestamp"))
              ->  Append  (cost=1045.72..730267.59 rows=1322778 width=40) (actual time=9.763..157.310 rows=11935 loops=1)
                    ->  Hash Join  (cost=1045.72..488644.07 rows=759884 width=40) (actual time=9.762..148.814 rows=11935 loops=1)
                          Hash Cond: (rd.variable_id = v.id)
                          ->  Append  (cost=161.53..483865.09 rows=759884 width=20) (actual time=0.567..112.737 rows=11935 loops=1)
                                ->  Bitmap Heap Scan on _hyper_21_7988_chunk rd  (cost=161.53..8872.25 rows=10312 width=20) (actual time=0.091..0.092 rows=0 loops=1)
                                      Recheck Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                      ->  Bitmap Index Scan on "7988_11867_raw_data_temp_pk"  (cost=0.00..158.95 rows=10312 width=0) (actual time=0.088..0.088 rows=0 loops=1)
                                            Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                          ->  Hash  (cost=609.64..609.64 rows=21964 width=4) (actual time=9.134..9.135 rows=22086 loops=1)
                                Buckets: 32768  Batches: 1  Memory Usage: 1033kB
                                ->  Seq Scan on variables v  (cost=0.00..609.64 rows=21964 width=4) (actual time=0.011..4.067 rows=22086 loops=1)
                    ->  Hash Join  (cost=1037.88..208554.06 rows=562894 width=40) (actual time=6.581..6.631 rows=0 loops=1)
                          Hash Cond: (m.variable_id = v_1.id)
                          ->  Append  (cost=153.69..204784.75 rows=562894 width=20) (actual time=6.579..6.629 rows=0 loops=1)
                                ->  Bitmap Heap Scan on _hyper_22_7886_chunk m  (cost=153.69..6401.67 rows=9339 width=20) (actual time=0.108..0.108 rows=0 loops=1)
                                      Recheck Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                                      ->  Bitmap Index Scan on "7886_12260_measures_temp_pk"  (cost=0.00..151.36 rows=9339 width=0) (actual time=0.107..0.107 rows=0 loops=1)
                                            Index Cond: (variable_id = ANY ('{23388,23389,23390,23391,23392,23393,23394,23395,23396,23397,23398,23399,23400,23401,23402,23403,23404,23405,23406,23407,23408,23409,23410,23411,23412,23413,23414,23415,23416,23417,23418}'::integer[]))
                          ->  Hash  (cost=609.64..609.64 rows=21964 width=4) (never executed)
                                ->  Seq Scan on variables v_1  (cost=0.00..609.64 rows=21964 width=4) (never executed)
Planning Time: 227.913 ms
JIT:
  Functions: 2008
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 670.038 ms, Inlining 59.746 ms, Optimization 17687.622 ms, Emission 11080.302 ms, Total 29497.708 ms
Execution Time: 29768.219 ms

Stack Exchange Network

Slow query for multiple data at same timestamp

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
postgresql
query-performance
aggregate
timescaledb
time-series-database
or ask your own question.

Hot Network Questions

Slow query for multiple data at same timestamp

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresqlquery-performanceaggregatetimescaledbtime-series-database or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
postgresql
query-performance
aggregate
timescaledb
time-series-database
or ask your own question.