Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2786582
  • 博文数量: 423
  • 博客积分: 7770
  • 博客等级: 少将
  • 技术积分: 4766
  • 用 户 组: 普通用户
  • 注册时间: 2006-11-09 11:58
个人简介

Oracle/DB2/Postgresql/Mysql/Hadoop/Greenplum/Postgres-xl/Mongodb

文章分类

全部博文(423)

文章存档

2019年(3)

2018年(6)

2017年(27)

2016年(23)

2015年(30)

2014年(16)

2013年(31)

2012年(73)

2011年(45)

2010年(14)

2009年(30)

2008年(30)

2007年(63)

2006年(32)

分类: Mysql/postgreSQL

2019-04-02 18:09:58

Use this:


SELECT COUNT(*) FROM (SELECT DISTINCT column_name FROM table_name) AS temp;


This is much faster than:


COUNT(DISTINCT column_name)


SQL 例子:


qis3_dp2=# explain analyze SELECT COUNT(*),SMTOC FROM ( SELECT 
DISTINCT(SVIN) AS CHECKCARNUM,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=1 
AND A.SSTATIONCD = 'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND 
A.DWORKDATE >= TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <= 
TO_DATE('2019-03-11','YYYY-MM-DD')) AS TEMP  group by SMTOC; 
                                                                                                                          
QUERY PLAN                                                                               
                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
--------------------------------------------- 
 HashAggregate  (cost=691386.41..691388.41 rows=200 width=30) (actual 
time=4090.951..4091.027 rows=410 loops=1) 
   Group Key: a.smtoc 
   ->  HashAggregate  (cost=666561.44..676491.43 rows=992999 width=40) 
(actual time=3481.712..3794.213 rows=1071367 loops=1) 
         Group Key: a.svin, a.smtoc 
         ->  Gather  (cost=1000.00..656098.93 rows=2092501 width=40) (actual 
time=0.657..1722.814 rows=2142215 loops=1) 
               Workers Planned: 4 
               Workers Launched: 4 
               ->  Parallel Seq Scan on qis_carpassedstation a 
(cost=0.00..445848.83 rows=523125 width=40) (actual time=65.187..2287.739 
rows=428443 loops=5) 
                     Filter: (((sstationcd)::text = 'VQ3_LYG'::text) AND 
((slineno)::text = ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >= 
to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <= to_da 
te('2019-03-11'::text, 'YYYY-MM-DD'::text))) 
                     Rows Removed by Filter: 1862173 
 Planning Time: 0.513 ms 
 Execution Time: 4147.542 ms 
(12 rows) 

Time: 4148.852 ms (00:04.149) 
qis3_dp2=# 


qis3_dp2=# SELECT COUNT(*),SMTOC FROM ( SELECT DISTINCT(SVIN) AS 
CHECKCARNUM,SMTOC FROM QIS_CARPASSEDSTATION A WHERE 1=1 AND A.SSTATIONCD = 
'VQ3_LYG' AND A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >= 
TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <= 
TO_DATE('2019-03-11','YYYY-MM-DD')) AS TEMP  group by SMTOC; 

**Time: 3223.935 ms (00:03.224)** 


2、 调整之前

qis3_dp2=# explain analyze SELECT COUNT(DISTINCT SVIN) AS CHECKCARNUM ,SMTOC 
FROM QIS_CARPASSEDSTATION A WHERE 1=1 AND A.SSTATIONCD = 'VQ3_LYG' AND 
A.SLINENO IN ( '1F' , '2F' , '3F' ) AND A.DWORKDATE >= 
TO_DATE('2017-02-11','YYYY-MM-DD') AND A.DWORKDATE <= 
TO_DATE('2019-03-11','YYYY-MM-DD') group by  SMTOC; 
                                                                                                                          
QUERY PLAN                                                                               
                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
--------------------------------------------- 
 GroupAggregate  (cost=875778.02..891475.55 rows=377 width=30) (actual 
time=6400.991..33314.132 rows=410 loops=1) 
   Group Key: smtoc 
   ->  Sort  (cost=875778.02..881009.28 rows=2092501 width=40) (actual 
time=6399.993..6626.151 rows=2142215 loops=1) 
         Sort Key: smtoc 
         Sort Method: quicksort  Memory: 265665kB 
         ->  Gather  (cost=1000.00..656098.93 rows=2092501 width=40) (actual 
time=0.557..2467.778 rows=2142215 loops=1) 
               Workers Planned: 4 
               Workers Launched: 4 
               ->  Parallel Seq Scan on qis_carpassedstation a 
(cost=0.00..445848.83 rows=523125 width=40) (actual time=66.908..2428.397 
rows=428443 loops=5) 
                     Filter: (((sstationcd)::text = 'VQ3_LYG'::text) AND 
((slineno)::text = ANY ('{1F,2F,3F}'::text[])) AND (dworkdate >= 
to_date('2017-02-11'::text, 'YYYY-MM-DD'::text)) AND (dworkdate <= to_da 
te('2019-03-11'::text, 'YYYY-MM-DD'::text))) 
                     Rows Removed by Filter: 1862173 
 Planning Time: 0.457 ms 
 Execution Time: 33335.429 ms 
(13 rows) 

Time: 33336.720 ms (00:33.337)* 
qis3_dp2=# 

阅读(1447) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~