PostgreSQL 10.3下优化EAV表非完全重复行查询（替代DISTINCT）

好的，针对你在PostgreSQL 10.3中处理超大EAV表的这个需求，我有几个高效的替代方案，完全不用依赖SELECT DISTINCT，而且能大幅降低查询开销。先理清楚核心需求：我们要找出所有entity_id和attribute_name相同但value不同的行，同时完全重复的（三列全同）行只保留一行。

方案一：GROUP BY + EXISTS 子查询（推荐，性能最优） #

这个方案利用GROUP BY天然的去重特性，再结合EXISTS筛选出存在多值的实体-属性组合，比DISTINCT更高效，尤其是在有合适索引的情况下：

SELECT entity_id, attribute_name, value
FROM eav_table e
WHERE EXISTS (
    SELECT 1
    FROM eav_table e2
    WHERE e2.entity_id = e.entity_id
      AND e2.attribute_name = e.attribute_name
      AND e2.value <> e.value
)
GROUP BY entity_id, attribute_name, value;

原理说明：

• EXISTS子查询快速判断当前的(entity_id, attribute_name)是否存在至少一个不同的value，过滤掉那些只有单一值的组合；
• GROUP BY (entity_id, attribute_name, value)自动帮我们去重，只保留每个唯一的三元组，效果和DISTINCT一致，但PostgreSQL对GROUP BY的索引利用更友好，能避免不必要的排序操作。

方案二：窗口函数筛选唯一行 #

如果你更倾向于用窗口函数的写法，也可以这样实现，逻辑更直观：

WITH unique_rows AS (
    SELECT 
        entity_id, 
        attribute_name, 
        value,
        -- 给每个重复的三元组标记唯一行号
        ROW_NUMBER() OVER (PARTITION BY entity_id, attribute_name, value ORDER BY (SELECT NULL)) AS rn,
        -- 统计当前实体-属性组合下的不同value数量
        COUNT(DISTINCT value) OVER (PARTITION BY entity_id, attribute_name) AS value_count
    FROM eav_table
)
SELECT entity_id, attribute_name, value
FROM unique_rows
WHERE rn = 1 AND value_count > 1;

原理说明：

• 先用ROW_NUMBER()给每个重复的(entity_id, attribute_name, value)组标记行号，取rn=1就得到了去重后的行；
• COUNT(DISTINCT value)窗口函数统计每个实体-属性组合下的不同value数量，只保留数量大于1的组，确保符合“value不同”的条件。

关键优化：一定要加索引！ #

不管用哪个方案，针对超大表，索引是性能提升的核心。创建这个复合索引：

CREATE INDEX idx_eav_entity_attr_value ON eav_table (entity_id, attribute_name, value);

这个索引能让查询直接从索引中读取所需数据，避免全表扫描，同时GROUP BY和窗口函数的分区操作都能利用索引的有序性，大幅减少CPU和内存消耗。

额外建议：清理重复行（可选） #

如果你的表中有大量重复的三元组行，建议先做一次重复行清理，这样后续查询的性能会更上一层楼：

DELETE FROM eav_table
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM eav_table
    GROUP BY entity_id, attribute_name, value
);

这个操作会删除所有重复的行，只保留每个三元组的第一行，适合数据插入后重复行不会再大量产生的场景。

内容的提问来源于stack exchange，提问作者Noah Rose Ledesma