我正在通过重新排序 PostgreSQL 中的复合索引来优化 SQL 查询性能。我需要了解潜在的影响,包括空间使用情况、数据保存开销以及此修改的任何其他影响。
为了解决性能问题,我实施了一种涉及重新排序复合索引的方法。
详细信息:数据库: PostgreSQL表名: readingTable DDL 语句:
CREATE TABLE readingTable (
ts timestamp NOT NULL,
plant_id int8 NOT NULL,
instance_type varchar(30) NOT NULL,
instance_id int8 NOT NULL,
readings jsonb NULL,
CONSTRAINT idx_readingTable_pk PRIMARY KEY (ts, plant_id, instance_type, instance_id));
CREATE INDEX readingTable_ts_idx ON readingTable USING btree (ts DESC);
表格数据来源:
特斯 | 植物编号 | 实例类型 | 实例 ID | 阅读 |
---|---|---|---|---|
2024-05-31 23:59:00.000 | 4 | 警报 | 1765 | [{"c": "ALARM_TAG_1", "d": "NUMBER", "s": "S15", "u": "kW/kWp", "v": null}, {"c": "ALARM_TAG_2", "d": "BOOLEAN", "s": "S00", "u": "AU", "v": "false"}, {"c": "ALARM_TAG_3", "d": "NUMBER", "s": "S00", "u": "kW/kWp", "v": "12.25"}] |
询问:
SELECT *
FROM readingTable, LATERAL jsonb_array_elements(readings) AS readings_data
WHERE plant_id = 2
AND instance_type = 'ALARM'
AND instance_id IN (1765)
AND ts BETWEEN '2024-05-01 00:00:00' AND '2024-05-30 23:59:00.000'
AND readings_data ->> 'c' IN ('ALARM_TAG_1')
ORDER BY ts, instance_id;
如果我对 idx_readingTable_pk(ts, plant_id, instance_type, instance_id) 运行上述查询,那么执行查询需要时间,因此我已将idx_readingTable_pk 从 (ts, plant_id, instance_type, instance_id) 更新为 (plant_id, instance_type, instance_id, ts)。
在更新后的索引中,我重新排序了组合键的序列,因为当条件包括相等性检查(=)和范围/不等性检查(>、>=、<、<=、IN)时,涉及相等性检查的列应首先出现在索引中,不等式列则紧随其后。
查询更新 idx_readingTable_pk:
-- Drop the old primary key constraint
ALTER TABLE readingTable
DROP CONSTRAINT idx_readingTable_pk;
-- Add the new primary key constraint with the updated column order
ALTER TABLE readingTable
ADD CONSTRAINT idx_readingTable_pk PRIMARY KEY (plant_id, instance_type, instance_id,ts);
此项修改已导致性能提升,如以下统计数据所示。在下图中,您可以看到我已在不同日期范围内使用旧索引和新索引测试了查询,您可以观察到性能提升。
问题:
- 第一次执行查询来更新复合键顺序时,它会更新现有索引、创建新索引还是两者兼而有之?
- 是否涉及任何空间复杂性,例如与旧索引相比,存储更新索引的空间要求增加了?
- 在保存数据时,重新排序索引会带来任何开销吗?
- 修改综合指数还有哪些其他潜在影响需要我注意?
每次修改表时都会更新索引。如果从头开始重新创建索引,性能将难以承受。
新索引的大小与旧索引大致相同。由于新索引尚未膨胀,因此新索引最初会较小,但随着时间的推移,大小会趋于平衡。
索引中列的顺序不会影响创建或更新索引的性能。
更改索引列的顺序会对查询的性能产生巨大影响。例如,此查询:
使用原始主键会非常快,但使用新的主键会慢得多。