Postgresql
同一時間戳下多條數據的慢查詢
大家好，
我目前面臨一個問題，即有效地查詢我的數據庫以檢索同一時間戳的所有行。
我會解釋，
我有兩個使用 TimescaleDb Hypertable 的數據表，其架構如下：
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');數據庫大小：
- 原始數據：106 524 480 行
- 措施：24 942 692 行
數據庫索引：
原始數據：
- raw_data_pk ：（時間戳，值，variable_id）
- raw_data_variable_id：（時間戳，variable_id）
措施
- measure_pk ：（時間戳，值，variable_id）
- measure_variable_id：（時間戳，variable_id）
我有一個包含 3 個參數的查詢：
- variablesId（整數列表）
- 限制（要檢索的時間戳數）
- 最後日期（日期）
- timeZone（我們要檢索數據的時間）
目標是檢索所有時間戳列表的每個同步時間戳，如下所示：
[{ timestamp: xxxxx, data: [ { variable_id: yy1, value: zz1 }, { variable_id: yy2, value: zz1 }], etc.. }]今天我使用以下查詢：
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], };這給了我以下解釋分析：
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[]))但是這個查詢很慢，我試圖找到一些優化點：
- 分別查詢所有變數？做聚合伺服器端嗎？
- 刪除慢的 json_agg ？但我無法應用限制，因為我不知道要選擇的行數以使每個變數具有相同的時間戳
- 我應該刪除兩個表上的聯合嗎？
- 我應該在時區刪除之前設置時區嗎？
今天對 10 個變數和 10 個限制的查詢大約需要 15 秒，這對使用者來說非常慢。
我願意接受任何建議，任何測試。提前謝謝你祝你有美好的一天。
我認為問題很可能是您的鑰匙是這樣訂購的
timestamp, variable_id, value但是您的查詢選擇條件領先
variable_id這意味著計劃不能使用索引，因為索引排序首先是時間戳，然後是 variable_id
如果您切換它們以便鍵被排序為
variable_id, timestamp, value（或什variable_id, value, timestamp至）並再次執行解釋，您應該看到該計劃現在可以variable_id從索引中使用。如果您在 PostgreSQL 中搜尋多列索引，這應該有助於解釋推理。如果是生產數據庫，請先在測試或開發環境中試用！應用程序的其他部分可能假定
timestamp為前導列。
在@greenweeds 回答之後，我將架構更改如下：
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');所以我改變了我的主鍵的順序，然後我的索引。成功了，我的查詢獲得了 10 秒的時間。
這是查詢計劃的新精簡版：
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