在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包含两部分：
其执行步骤如下：
利用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运维技术
在pg中，distinct关键字与select语句一起使用，用于去除重复记录。然而postgresql目前缺乏从有序索引中有效提取唯一值列表的能力, 它需要扫描整个索引来找到唯一的值。随着表的增长，这个操作会很慢。对一个大表（几百万行甚至更大）的某个字段进行distinct操作，其执行计划可能如下图所示正如我们所知，HashAggregate通过在内存构建数据的哈希表来对数据进行分组，而这通常会打来较大的性能损耗。针对distinct或group by慢的问题，mysql中提供了Loo.
【PGSQL】pgsql中distinct与distinct on的用法
最近接到个需求，要求对考核成绩进行排名，一个用户可能有多条记录,需求要有去重，取最新的一条记录，成绩排名倒是清楚怎么写，去重着实难倒了我。
在万能的百度下，我找到了一位大佬写的文章，链接如下:
pgsql中distinct与distinct on的用法
distinct与distinct on的区别与联系
distinct的用法
语法：select distinct 列名[, 列名…] from 表名
distince作用: 列
废话不多说，直击问题处理过程...
原SQL，多表关联，order表4w，order_trace表24w数据，按照正常逻辑，就算关联7表，查询其他表得字段很多，查询分页得到数据速度平均在1.300s，肯定存在问题。
SELECT DISTINCT
(a.order_no),
a.`name` order_name,
a.eid,
a.ui...
表共800W数据，从260W的结果集中计算出不同的案件数量(130万)，需要执行20多秒
原SQL内容
select count(distinct c_bh_aj) as ajcount
from db_znspgl.t_zlglpt_wt
where d_cjrq between '20160913' and '20170909';
表信息和数据量
连接mysql报“com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException”的错误解决
m0_75009818:
从任一结点出发到根的路径上所经过的结点序列按其关键字有序
白白的白:
PostgreSQL之双冒号(::)的含义
ylp2004:
PostgreSQL之FULL_PAGE_WRITES
python GMT时间格式转化
牧876:
