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

一尘不染

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

sql

不知道在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
);

并在每次插入数据时使用触发器来更新它。

阅读 198

2021-03-10

共1个答案

一尘不染

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

但是，由于这与性能有关，因此有另一种解决方案：您可以使用 递归CTE来模拟松散索引扫描：

WITH RECURSIVE cte AS (
   (  -- parentheses required
   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
   WHERE  c.day IS NOT NULL  -- exit condition
   )
SELECT day FROM cte;

SELECT由于附带了ORDER BY和LIMIT子句，因此需要在第一个括号中加上括号。

只需在上有一个普通索引day。

2021-03-10