PostgreSQL中COUNT的各条件下(1亿条数据)例子

转自:

https://www.cnblogs.com/jacker1979/p/4661125.html

插入一亿条数据

(示例数据库:9.3.5)
参考资料:http://www.oschina.net/question/96003_70381

1
2
3
4
test=# insert into tbl_time1 select generate_series(1,100000000),clock_timestamp(),now();
INSERT 0 100000000
Time: 525833.218 ms
约:8.7分钟

COUNT,没有索引,1亿条数据。

1
2
3
4
5
6
7
test=# select count(1) from tbl_time1;
   count
-----------
 100000000
(1 row)
Time: 3070658.058 ms
约:51.2分钟

添加主键索引耗时

1
2
3
4
test=# alter table tbl_time1 add primary key (id);
ALTER TABLE
Time: 981276.804 ms
约:16.4分钟

COUNT,有索引(主键),1亿条数据,注意 where id > 0 的条件

1
2
3
4
5
6
7
8
这个有 where id > 0
test=#  select count(id) from tbl_time1 where id > 0;
   count
-----------
 100000000
(1 row)
Time: 244243.112 ms
约:4.071分钟

COUNT,有索引(主键),1亿条数据,注意没有 where id > 0 的条件

1
2
3
4
5
6
7
8
这个无 where id > 0
test=#  select count(id) from tbl_time1;
   count
-----------
 100000000
(1 row)
Time: 548650.606 ms
约:9.144分钟

通过修改配置文件调优postgresql.conf

1
2
3
4
5
6
7
8
9
10
11
enable_bitmapscan = off
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
enable_seqscan = off
#enable_sort = on
enable_tidscan = off
1
2
3
4
5
6
7
test=# select count(id) from tbl_time1 where id > 0;
   count
-----------
100000000
(1 row)
Time: 87501.151 ms
约:1.456分钟

论count使用不当的罪名 和 分页的优化

转自:http://blog.163.com/digoal@126/blog/static/1638770402016468349463

分页是一个非常常见的应用场景,然而恐怕没有多少人想过其优化方法。
确一味的责怪为什么数据库用count(*)计算分页数是如此的慢。
很多开发人员喜欢用count先算一下结果集的大小,然后就知道需要排多少页。
然后再从数据库取出对应的数据,并展示给用户。
问题1
count会扫一遍数据,然后取数据又扫一遍数据。重复劳动。
问题2,很多人喜欢用order by offset limit来展示分页。
其实也是一个非常大的问题,因为扫描的数据也放大了,即使在order by 的列上用到了索引也会放大扫描的数据量。
因为offset的row也是需要扫的。

问题1的优化
使用评估行数,方法如下
创建一个函数,从explain中抽取返回的记录数

CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS
$func$
DECLARE
    rec   record;
    ROWS  INTEGER;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN ROWS IS NOT NULL;
    END LOOP;

    RETURN ROWS;
END
$func$ LANGUAGE plpgsql;

评估的行数和实际的行数相差不大,精度和柱状图有关。
PostgreSQL autovacuum进程会根据表的数据量变化比例自动对表进行统计信息的更新。
而且可以配置表级别的统计信息更新频率以及是否开启更新。

postgres=# select count_estimate('select * from sbtest1 where id between 100 and 100000');
 count_estimate 
----------------
         102166
(1 row)

postgres=# explain select * from sbtest1 where id between 100 and 100000;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Index Scan using sbtest1_pkey on sbtest1  (cost=0.43..17398.14 rows=102166 width=190)
   Index Cond: ((id >= 100) AND (id <= 100000))
(2 rows)

postgres=# select count(*) from sbtest1 where id between 100 and 100000;
 count 
-------
 99901
(1 row)

也就是说,应用程序完全可以使用评估的记录数来评估分页数。
这样做就不需要扫描表了,性能提升尤为可观。

问题2的优化
问题2其实表现在数据可能被多次扫描,使用游标就能解决。
未优化的情况,取前面的记录很快。

postgres=# explain analyze select * from sbtest1 where id between 100 and 1000000 order by id offset 0 limit 100;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..9.74 rows=100 width=190) (actual time=0.019..0.088 rows=100 loops=1)
   ->  Index Scan using sbtest1_pkey on sbtest1  (cost=0.43..93450.08 rows=1003938 width=190) (actual time=0.018..0.051 rows=100 loops=1)
         Index Cond: ((id >= 100) AND (id <= 1000000))
 Planning time: 0.152 ms
 Execution time: 0.125 ms
(5 rows)

取后面的记录,因为前面的记录也要扫描,所以明显变慢。

postgres=# explain analyze select * from sbtest1 where id between 100 and 1000000 order by id offset 900000 limit 100;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=83775.21..83784.52 rows=100 width=190) (actual time=461.941..462.009 rows=100 loops=1)
   ->  Index Scan using sbtest1_pkey on sbtest1  (cost=0.43..93450.08 rows=1003938 width=190) (actual time=0.025..308.865 rows=900100 loops=1)
         Index Cond: ((id >= 100) AND (id <= 1000000))
 Planning time: 0.179 ms
 Execution time: 462.053 ms
(5 rows)

如果有很多个分页,效率下降可想而知。

优化手段

postgres=# begin;
BEGIN
Time: 0.152 ms
postgres=# declare cur1 cursor for select * from sbtest1 where id between 100 and 1000000 order by id;
DECLARE CURSOR
Time: 0.422 ms
postgres=# fetch 100 from cur1;
。。。

获取到数据末尾时,效率也是一样的不会变化。

不觉进入到这个项目已经半年了

从2017年4月底,进入到这个跨国公司的这个项目,不觉已经半年了,半年来,项目中用到的技术,基本是以前积累,没学到什么实质性的提高。但是对于DBA在这个项目中占主导地位的现实,深感无奈,天天想骂娘,不过,看在钱的份上,做到良心过得去,也就算了。

 

所有的事情最后都可以归结到人管人,所有的商业模式最后比的都是人均revenue

在一个大组织的一个几百万的项目上工作了3个半月,真心觉得太累。以下是几点感悟:

(1)找到合适的人,比较难,最终任何事都得归结到 人管人上。

(2)你管合适的人,合适的人再去管合适的人,链条太长,企业管理增效减错的过程,就是把人管人的链条尽量减少的过程。微服务、Servless 模式 原理应该是一样的,减少互相依赖的链条,尽量做到自动化(运维),直至开发。

(3)好的家庭,招就那几个架势,不爽的家庭,各有各的不爽,最终都是人管人出问题了,核心就是企业文化和价值观。

(4)中心架构–>业务管理难(人管人太难),所以去中心化–>所以微服务,Servless,所以有什么阿米巴管理模式,管理–>技术 一一呼应,对应的上,对不对。

(5)该大组织的人,个人素质还可以,但没有卓越的人领着,卓越的人下面,也没有优秀的人带队,形成自己的战斗方阵和策略。这些外来的顾问,个人能力可能都还凑合,但是合在一起,战斗力上不去。所以谷歌、百度得花 5倍、10倍的价格从大海中捞到金子,即使像 乐视那样,花了高倍的钱招聘过来一些牛人,但是底子差(创始人不是斯坦福毕业的),眼界不行,所以招聘的人也不行,人管人不行。

(6)综上所述,创业真的太难了。找钱,找人,找方向。

空降一个团队最大的问题

既然空降一个团队,那么请你过来的老板,必然对你期望很大。既然是空降,手中必然是一副烂牌,2个王,4个2的团队是不可能的。

既然是空降,手中又是一副烂牌,这都不是最可怕的,最可怕的,还是手中这些烂牌,自己一个还都动不了。在这种情况下,空降之前,一定要把自己手中的牌了解清楚,如果资源一个动不了,就不要过去。