PostgreSQL + TimescaleDB 方案不按主键索引走，有什么办法吗？

[LeeReamond]

如题，新手接触 TimescaleDB ，目前是用这套方案做了一套存音频传感器的数据的工具，大概有 100 个左右的传感器，存二进制数据，日增大概十万行左右。

目前遇到比较大一个问题是，常见的搜索场景是“搜索传感器 A 在某月某日到某月某日期间的所有记录”。但是目前搜索优化器是先从时间的总序列开始查，然后再筛选其中的 A 传感器。而我觉得逻辑上显然是先搜索 A 传感器再筛选时间更优。

前者不光是慢的问题，而且现在有个大问题是走时间索引的话，每个操作占用锁的数量太多了，就算把参数调大了，日常使用也经常报错。

问下万能的 v2 ，有什么办法解决吗？

附： 创建表的命令：

CREATE TABLE sensor_data (
    ts          TIMESTAMPTZ       NOT NULL,
    sensor_id   INTEGER           NOT NULL,
    data        VARCHAR(64)       NOT NULL,

    PRIMARY KEY (sensor_id, ts)
);

SELECT create_hypertable(
    'sensor_data',
    'ts',
    partitioning_column => 'sensor_id',
    number_partitions => 16,  
    chunk_time_interval => INTERVAL '7 day' 
);

解释搜索命令得到如下结果：

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sensor_data
WHERE sensor_id = '001'
  AND ts >= '2024-12-06 00:00:00+08'::timestamptz
  AND ts <  '2024-12-07 00:00:00+08'::timestamptz;


                                                                           QUERY PLAN                                                                             

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Index Scan using _hyper_3_23024_chunk_sensor_data_ts_idx on _hyper_3_23024_chunk  (cost=0.28..6.71 rows=144 width=45) (actual time=0.032..0.062 rows=144 loops=1)
   Index Cond: ((ts >= '2024-12-06 00:00:00+08'::timestamp with time zone) AND (ts < '2024-12-07 00:00:00+08'::timestamp with time zone))
   Filter: (sensor_id = 1)
   Buffers: shared hit=6
 Planning:
   Buffers: shared hit=1076 read=1
 Planning Time: 7.689 ms
 Execution Time: 11.201 ms
(8 rows)

如各位所见的就是不管怎么调，一开始都是走 ts_idx 索引，然后再 filter sensor_id

感觉 timescaleDB 是个好东西，但是这个问题卡住完全没法用啊。拜谢各位

[siaronwang]

pg 应该已经安装 sensor_id 找到了 hyper_3_23024_chunk 块，执行计划的成本优化导致的选择吧。而且这个查询 sensor_id 是字符串，你 sensor_id INTEGER NOT NULL, 这里是 integer 是不是导致索引失效了呢。

[siaronwang]

另外可以创建一个针对`CREATE INDEX ON sensor_data (sensor_id, ts DESC);` 这样索引 可以试试

[LeeReamond]

@siaronwang 感谢回复，类型是检查了一下数据类型是 varchar ，上面写错了，应该是不是导致索引失效。具体索引的话，创建另一个 index 也没改变。

目前问题是，搜索效率是可以的，就是占用锁太多了，我得把最大锁数量提高到几十万才能勉强运行程序，太头疼了。。

[Chance123]

为什么不用其它时序库做底层存储？比如 influxdb 之类的，他们都能原生按照设备拆分，我早年前也因为 pg+sql 用过 timescaledb 问题在于占用磁盘太大所以放弃。

[misakiyusa]

sensor_id = '001' 这个条件有问题吧，sensor_id 类型是 integer ，那么正常来说查询条件里的 sensor_id 不应该是 1 ？

[clarkethan]

去掉 PRIMARY KEY (sensor_id, ts)，然后在 create_hypertable 之后单独建一个索引，CREATE INDEX sensor_data_sensor_ts_idx ON sensor_data (sensor_id, ts)，应该能大大减轻负担，包括你说的锁，也能更好的利用上索引，可以试试，唯一索引会加重锁占用，尤其是你的场景里面