在pg中，distinct关键字与select语句一起使用，用于去除重复记录。然而postgresql目前缺乏从有序索引中有效提取唯一值列表的能力, 它需要扫描整个索引来找到唯一的值。随着表的增长，这个操作会很慢。
对一个大表（几百万行甚至更大）的某个字段进行distinct操作，其执行计划可能如下图所示
正如我们所知，HashAggregate通过在内存构建数据的哈希表来对数据进行分组，而这通常会打来较大的性能损耗。
针对distinct或group by慢的问题，mysql中提供了Loose indexscan, oracle提供了index skip scan的优化方案。
当数据库具有“index Skip Scan”或“loose indexscan”这样的特性时，它可以从一个有序值增量地跳转到下一个有序值，而无需读取两者之间的所有行。如果不支持这个特性，数据库引擎就必须扫描整个有序索引，然后在最后进行重复数据删除——这是一个慢得多的过程。
然而postgres本身并不支持这种松散索引的方案（注：官方已经在计划支持中, 见：https://commitfest.postgresql.org/19/1741/）。
目前，针对pg的话，可以使用rescursive CTE来加快distinct查询的速度。见：https://wiki.postgresql.org/wiki/Loose_indexscan
什么是CTE
CTE(common table expressions) 用于简化复杂查询。可以在其他SQL中被引用，其结果仅存在于查询执行期间。
创建CTE的语法如下：
WITH cte_name (column_list) AS (
CTE_query_definition
)
statement;
cte_name:指定CTE的名称，column_list是可选的列字段列表。
CTE_query_definition：指定查询作为返回结果集，如果没有显示指定列字段列表，则CTE_query_definition 的select 字段列表将作为CTE的字段列表。
statement: 在其他SQL语句中可以像使用表或视图一样使用CTE，语句可以是SELECT, INSERT, UPDATE 和 DELETE。
什么是rescursive CTE
CTE一个重要的是特性就是递归。使用Recursive关键字，with查询可以引用它自己的输出，从而实现递归。我们看一个从1到100的整数求和功能。
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
Recursive with语句的CTE_query_definition包含两部分：
non-recursive term，非递归部分，即上例中的union前面的部分。
recursive term（递归部分），即上例中union后面部分。
其执行步骤如下：
执行non-recursive term。其结果作为recursive term中对result的引用，同时将这部分结果放入临时的working table中
重复执行如上步骤，直到working table为空：用working table的内容替换递归的自引用，执行recursive term，并用该结果替换working table。
利用rcte替换distinct
如https://wiki.postgresql.org/wiki/Loose_indexscan中提到的方案，如下所示语句等同于SELECT DISTINCT col FROM tbl;
WITH RECURSIVE t AS (
SELECT min(col) AS col FROM tbl
UNION ALL
SELECT (SELECT min(col) FROM tbl WHERE col > t.col)
FROM t WHERE t.col IS NOT NULL
)
SELECT col FROM t WHERE col IS NOT NULL
UNION ALL
SELECT null WHERE EXISTS(SELECT 1 FROM tbl WHERE col IS NULL);
如果col字段是非null，会稍微简单点。
WITH RECURSIVE t AS (
(SELECT col FROM tbl ORDER BY col LIMIT 1) -- parentheses required
UNION ALL
SELECT (SELECT col FROM tbl WHERE col > t.col ORDER BY col LIMIT 1)
FROM t
WHERE t.col IS NOT NULL
)
SELECT col FROM t WHERE col IS NOT NULL;
我有一张几千万的表events，要查询distinct project_id，我们分别看看这两种方式的执行计划。
可以看到使用rescursive CTE的性能提升效果还是非常显著的。
虽然说递归cte可以帮助我们提升distinct的查询效率，但是如上文所示，编写cte语句通常会让人感觉麻烦和不直观。所以我们还是期待下后面的官方版本能引入“skip scan”这一特性吧。
参考：
https://www.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/ https://wiki.postgresql.org/wiki/Loose_indexscan https://blog.csdn.net/neweastsun/article/details/89608698
该篇已首发到公众号PostgreSQL运维技术，欢迎来踩~
悄悄放一张：
PostgreSQL运维技术
