• 基本情况
  表共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';
表信息和数据量
 
znspgl=# \d+ db_znspgl.t_zlglpt_wt
                            Table "db_znspgl.t_zlglpt_wt"
 Column  |          Type          | Modifiers | Storage  | Stats target | Description 
---------+------------------------+-----------+----------+--------------+-------------
 c_bh    | character(32)          | not null  | extended |              | 编号
 c_bh_aj | character(32)          |           | extended |              | 案件编号
 n_ajbs  | numeric(15,0)          |           | main     |              | 案件标识
 c_zjgz  | character varying(600) |           | extended |              | 质检规则
 c_zjxm  | character varying(300) |           | extended |              | 质检项目
 d_cjrq  | date                   |           | plain    |              | 创建日期
Indexes:
    "pk_zlglpt_wt" PRIMARY KEY, btree (c_bh)
    "i_t_zlglpt_wt_ajbs" btree (n_ajbs)
    "i_t_zlglpt_wt_bh_aj" btree (c_bh_aj)
    "i_t_zlglpt_wt_cjrq" btree (d_cjrq)
znspgl=# select count(*) from db_znspgl.t_zlglpt_wt
znspgl-# ;
  count  
---------
 8000000
(1 row)
数据库版本信息
 
znspgl=# select version();
                                                                 version                                                      
--------------------------------------------------------------------------------------------
 PostgreSQL 9.5.5 (ArteryBase 3.5.3, Thunisoft). on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-1
7), 64-bit
(1 row)
znspgl=# explain analyze select count(distinct  c_bh_aj) as ajcount from db_znspgl.t_zlglpt_wt where d_cjrq between '20160913' and '20170909';
                                                                     QUERY PLAN                                               
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=313357.40..313357.41 rows=1 width=33) (actual time=23478.562..23478.563 rows=1 loops=1)
   ->  Bitmap Heap Scan on t_zlglpt_wt  (cost=55811.21..306782.09 rows=2630125 width=33) (actual time=366.909..3946.452 rows=2
644330 loops=1)
         Recheck Cond: ((d_cjrq >= '2016-09-13'::date) AND (d_cjrq <= '2017-09-09'::date))
         Rows Removed by Index Recheck: 2670504
         Heap Blocks: exact=105741 lossy=105694
         ->  Bitmap Index Scan on i_t_zlglpt_wt_cjrq  (cost=0.00..55153.68 rows=2630125 width=0) (actual time=341.468..341.468
 rows=2644330 loops=1)
               Index Cond: ((d_cjrq >= '2016-09-13'::date) AND (d_cjrq <= '2017-09-09'::date))
 Planning time: 0.143 ms
 Execution time: 23478.624 ms
尝试增加覆盖索引
 
create index i_zlglpt_wt_zh01 on db_znspgl.t_zlglpt_wt (d_cjrq,c_bh_aj);
再次查看执行计划
 
znspgl=# explain analyze select count(distinct  c_bh_aj) as ajcount from db_znspgl.t_zlglpt_wt where d_cjrq between '20160913' and '20170909';
                                                                          QUERY PLAN                                          
------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Aggregate  (cost=134006.11..134006.12 rows=1 width=33) (actual time=21696.556..21696.557 rows=1 loops=1)
   ->  Index Only Scan using i_zlglpt_wt_zh01 on t_zlglpt_wt  (cost=0.56..127480.16 rows=2610380 width=33) (actual time=0.055.
.2684.807 rows=2644330 loops=1)
         Index Cond: ((d_cjrq >= '2016-09-13'::date) AND (d_cjrq <= '2017-09-09'::date))
         Heap Fetches: 0
 Planning time: 0.318 ms
 Execution time: 21696.604 ms
思考
 1、SQL速度提升很少！
 2、时间主要话费在Aggregate上了，时间从2648一下子升级到21696。
 3、理论上200W的count(distinct) 不应该花费19秒那么长时间，而且c_bh_aj还是有序的（建立索引了）
 
伪loose index scan
 
从网上看到一片帖子《分析MySQL中优化distinct的技巧》，count distinct 慢的原因是因为扫描编号时会扫描到很多重复的项，可以通过loose index scan避免这些重复的扫描（前提distinct项是有序的！），mysql 和 abase虽然不支持原生的loose index scan（oracle支持），但是可以通过改写SQL达到！
 
重新建立索引
 
drop index db_znspgl.i_zlglpt_wt_zh01;
create index i_zlglpt_wt_zh01 on db_znspgl.t_zlglpt_wt (c_bh_aj,d_cjrq);
改写SQL
 
select count(*) from  (
   select distinct(c_bh_aj)  
       from db_znspgl.t_zlglpt_wt 
       where d_cjrq between '20160913' and '20170909' 
查看执行计划
 
znspgl=# explain analyze select count(*) from  (select distinct(c_bh_aj)  from db_znspgl.t_zlglpt_wt where d_cjrq between '20160913' and '20170909' ) t;
                                                                             QUERY PLAN                                       
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=347567.23..347567.24 rows=1 width=0) (actual time=6954.845..6954.846 rows=1 loops=1)
   ->  Unique  (cost=0.56..343310.31 rows=340554 width=33) (actual time=0.034..5969.209 rows=1322165 loops=1)
         ->  Index Only Scan using i_zlglpt_wt_zh01 on t_zlglpt_wt  (cost=0.56..336784.36 rows=2610380 width=33) (actual time=
0.031..2840.502 rows=2644330 loops=1)
               Index Cond: ((d_cjrq >= '2016-09-13'::date) AND (d_cjrq <= '2017-09-09'::date))
               Heap Fetches: 0
 Planning time: 0.172 ms
 Execution time: 6954.890 ms
(7 rows)
通过timing 计算SQL执行时间
 
znspgl=# \timing on
Timing is on.
znspgl=#  select count(*) from  (select distinct(c_bh_aj)  from db_znspgl.t_zlglpt_wt where d_cjrq between '20160913' and '20170909' ) t;
  count  
---------
 1322165
(1 row)
Time: 1322.715 ms
通过伪loose index scan的SQL处理可以有效提高count(distinct)的执行速度！
求distinct是业务的一个普遍需求，例如每天有多少用户，每个省份有多少用户，每天有多少类目的用户等。
select date,count(dinstinct user) fr...
                                    可以看到，id相同的数据，保留ctid最小的，其他的删除。相当于把deltest表中的数据删掉一半，耗时达到67s多。可以看到同样是删除一半的数据，使用group by的方式，时间节省了一半。最容易想到的方法就是判断数据是否重复，对于重复的数据只保留ctid最小（或最大）的数据，删除其他的。group by方法通过分组找到ctid最小的数据，然后删除其他数据。测试环境验证，6600万行大表，删除2200万重复数据仅需3分钟。首先创建一张基础表，并插入一定量的重复数据。二、 PG中三种删除重复数据方法。
                                    零除的处理 用NULLIF(col, 0)可以避免复杂的WHEN...CASE判断. 对已知表可以增加Generated字段, 这些字段只读, 自动计算赋值, 可以像普通字段一样参与查询, 不需要在查询中实时计算, 是一种典型的使用空间换时间的优化方式.......
正如我们所知，HashAggregate通过在内存构建数据的哈希表来对数据进行分组，而这通常会打来较大的性能损耗。
针对distinct或group by慢的问题，mysql中提供了Loo.
                                    但是请记住，当增加max_parallel_workers_per_gather的数量并接近硬件的限制时，性能下降的风险。这一特性涉及好几个参数，但是，我们只聚焦在参数max_parallel_workers_per_gather。提高性能postgres运行时参数max_parallel_workers_per_gather来提高性能。注意，表的列数分别为1，5，10。当DISTINCT列的数量增加时，真正的差异出现了，如查询表t10所示。对于c2列，会根据c1的唯一性，从表中找到的第一个值。...
                                    当企业和公司面临SQL Server的性能挑战时，他们的重点通常是应用性能调整工具和优化技术。这不仅有助于分析和使查询运行得更快，而且有助于消除性能问题，排除性能不佳的故障。
                                    本文介绍了针对PostgreSQL数据库的优化技巧，从SQL查询优化、索引调整、服务器硬件配置等方面进行探讨，帮助读者了解如何最大化PostgreSQL数据库的性能和可靠性。其中包括了如何分析查询计划、优化索引、调整缓存和内存配置、使用分区表、负载均衡和高可用方案等技术。通过实际应用案例和实验结果，本文提供了一些可行的方案和经验教训，帮助读者解决PostgreSQL数据库性能问题和瓶颈。
                                    开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，软件架构师，软件开发大佬，可以解决你的问题。加群请联系 liuaustin3. （快满了，的准备开另一个群了）在MYSQL 中很少听说过自建统计信息，实际上在其他数据库中，创建统计信息的方式和需求都是有的，尤其处理复杂SQ...
SELECT '1' as id ,'201808' as m,'a' as k
union all
SELECT '2' as id ,'201808' as m,'a' as k
union all
SELECT '1' as id ,'201809' as m,'...
EXPLAIN命令可以查看执行计划，这个方法是我们最主要的调试工具。
2.及时更新执行计划中使用的统计信息
由于统计信息不是每次操作数据库都进行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL执行的时候会更新统计信息，
因此执行计划所用的统计信息很有可能比较旧。 这样执行计划的分析结果可能误差会变大。
以下是表tenk1的相关的一部分统计信息。
SELECT relname,