在非唯一列上创建唯一索引

提问于 2024-04-29T00:51:39+08:00

浏览次

2

不确定这是否可以在PostgreSQL 9.3中使用，但我想在非唯一列上创建一个唯一索引 . 对于像这样的表：

CREATE TABLE data (
  id SERIAL
  , day DATE
  , val NUMERIC
);
CREATE INDEX data_day_val_idx ON data (day, val);

我希望能够[快速]只查询不同的日子 . 我知道我可以使用 data_day_val_idx 来帮助执行不同的搜索，但是如果不同值的数量远远小于索引覆盖中的行数，这似乎会增加额外的开销 . 就我而言，30天内大约有1天是截然不同的 .

我唯一的选择是创建一个仅跟踪唯一条目的关系表吗？思维：

CREATE TABLE days (
  day DATE PRIMARY KEY
);

每次插入数据时都会用触发器更新它 .

1 回答

3
索引只能索引实际行，而不是聚合行 . 所以，是的，就所需的索引而言，创建一个具有您提到的唯一值的表是您唯一的选择 . 使用从 data.day 到 days.day 的外键约束强制引用完整性 . 这可能也是最佳性能，具体取决于完整的情况 .

但是，由于这似乎是 performance ，有一个替代解决方案：你可以使用 recursive CTE to emulate a loose index scan:
```
WITH RECURSIVE cte AS (
   (SELECT day FROM data ORDER BY 1 LIMIT 1)
   UNION ALL
   SELECT (SELECT day FROM data WHERE day > c.day ORDER BY 1 LIMIT 1)
   FROM   cte  c
   )
SELECT day FROM cte;
```
这只需要 day 上的普通索引 .

根据您的实际查询，有各种变体 . 细节：
- Optimize GROUP BY query to retrieve latest record per user
- Unused index in range of dates query
- Select first row in each GROUP BY group?
回复于 2024-04-29T00:51:39+08:00

相关问题