性能调优基础篇之select count(*) 与select count(1)

很多时候count(*) 与count(1)容易造成人误解

实验环境：　pg数据库 , 　

10万数量级

=> explain analyze select count(*) from city;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=5488.88..5488.89 rows=1 width=0) (actual time=13.690..13.690 rows=1 loops=1)
   ->  Seq Scan on city  (cost=0.00..5307.90 rows=72390 width=0) (actual time=0.004..8.382 rows=72390 loops=1)
 Total runtime: 13.726 ms
(3 rows)

Time: 23.646 ms
=> explain analyze select count(1) from city;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=5488.88..5488.89 rows=1 width=0) (actual time=16.912..16.912 rows=1 loops=1)
   ->  Seq Scan on city  (cost=0.00..5307.90 rows=72390 width=0) (actual time=0.005..9.382 rows=72390 loops=1)
 Total runtime: 16.952 ms
(3 rows)

Time: 20.035 ms                                                             

千万数量级

explain analyze select count(1) from xx;

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2144923.85..2144923.86 rows=1 width=0) (actual time=48684.707..48684.707 rows=1 loops=1)
   ->  Seq Scan on xx (cost=0.00..2102132.08 rows=17116708 width=0) (actual time=0.026..47239.822 rows=17154212 loops=1)
 Total runtime: 48691.623 ms
(3 rows)

Time: 48705.884 ms
hotel=>
 explain analyze select count(*) from xx;
^[[A                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2144923.85..2144923.86 rows=1 width=0) (actual time=65436.442..65436.442 rows=1 loops=1)
   ->  Seq Scan on xx  (cost=0.00..2102132.08 rows=17116708 width=0) (actual time=0.006..64207.656 rows=17154225 loops=1)
 Total runtime: 65445.016 ms
(3 rows)

Time: 65446.541 ms

结论：count(*)并不比count(1)性能差