在非唯一列上创建唯一索引 [英] Create a unique index on a non-unique column

问题描述

不确定在PostgreSQL 9.3+中是否可行，但是我想在非唯一列上创建唯一索引。对于这样的表：

Not sure if this is possible in PostgreSQL 9.3+, but I'd like to create a unique index on a non-unique column. For a table like:

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天与众不同。

I'd like to be able to [quickly] query only the distinct days. I know I can use data_day_val_idx to help perform the distinct search, but it seems this adds extra overhead if the number of distinct values is substantially less than the number of rows in the index covers. In my case, about 1 in 30 days is distinct.

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

Is my only option to create a relational table to only track the unique entries? Thinking:

CREATE TABLE days (
  day DATE PRIMARY KEY
);

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

And update this with a trigger every time we insert into data.

推荐答案

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

An index can only index actual rows, not aggregated rows. So, yes, as far as the desired index goes, creating a table with unique values like you mentioned is your only option. Enforce referential integrity with a foreign key constraint from data.day to days.day. This might also be best for performance, depending on the complete situation.

但是，因为这似乎与性能有关，还有一个替代解决方案：您可以使用递归CTE来模拟松散的索引扫描：

However, since this seems to be about performance, there is an alternative solution: you can use a 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;

这只需要在日的普通索引

有多种变体，具体取决于您的实际查询。详细信息：

There are various variants, depending on your actual queries. Details:

• 优化GROUP BY查询以检索每个用户的最新记录


• 日期范围查询中未使用的索引


• 选择每个GROUP BY组中的第一行？

• 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?

这篇关于在非唯一列上创建唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！