数据库►PostgreSQL

自行测试的1亿条数据中PostgreSQL性能

插入一亿条数据

1
2
3
4
5
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
8
9
test=# select count(1) from tbl_time1;
   count   
-----------
 100000000
(1 row)
Time: 3070658.058 ms
约:51.2分钟

添加主键索引耗时

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

再 explain 一下看看

1
2
3
4
5
6
test=# explain select count(id) from tbl_time1;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
 Aggregate  (cost=7770150.00..7770150.01 rows=1 width=4)
   ->  Seq Scan on tbl_time1  (cost=0.00..7520150.00 rows=100000000 width=4)
(2 rows)

虽然 id 上有索引，但是依然是使用顺序扫描。

COUNT，有索引(主键），1亿条数据，还要注意有没有 where id > 0 的条件的差别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
这个有 where id > 0
test=#  select count(id) from tbl_time1 where id > 0;
   count   
-----------
 100000000
(1 row)
Time: 244243.112 ms
约：4.071分钟
test=# explain select count(id) from tbl_time1 where id > 0;
                                          QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=7644075.89..7644075.90 rows=1 width=4)
   ->  Bitmap Heap Scan on tbl_time1  (cost=623925.90..7560742.56 rows=33333333 width=4)
         Recheck Cond: (id > 0)
         ->  Bitmap Index Scan on tbl_time1_pkey  (cost=0.00..615592.57 rows=33333333 width=0)
               Index Cond: (id > 0)
(5 rows)
 
Time: 0.767 ms
这个无 where id > 0
test=#  select count(id) from tbl_time1;
   count   
-----------
 100000000
(1 row)
Time: 548650.606 ms
约：9.144分钟
test=# explain select count(id) from tbl_time1;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
 Aggregate  (cost=7770150.00..7770150.01 rows=1 width=4)
   ->  Seq Scan on tbl_time1  (cost=0.00..7520150.00 rows=100000000 width=4)
(2 rows)
 
Time: 1.253 ms

COUNT 结论：（9.3.5的版本，默认配置）

无论你的数据有没有索引，COUNT都只会进行全表扫描。（条件是没有where， 或有where，但经过查询计划估算代价时，还是决定使用顺序全表扫描）。比如在上面的表，id 有索引。大概原因是说：因为MVCC的影响。

select count(id) from tbl_time1 where id > 0;
经过PG的查询优化器估算时，它最后还是决定使用 Seq Scan 扫描。

1
2
3
4
5
6
7
8
9
test=# explain select count(id) from tbl_time2 where id > 0;
                               QUERY PLAN                               
-------------------------------------------------------------------------
 Aggregate  (cost=21370.00..21370.01 rows=1 width=4)
   ->  Seq Scan on tbl_time2  (cost=0.00..18870.00 rows=1000000 width=4)
         Filter: (id > 0)
(3 rows)
 
Time: 0.872 ms
Postgres中通过需要扫描来计数count(*)的成本比较高. 没有别的办法来来对行数计数并返回结果除了扫描全部数据.

通过修改配置文件调优，时间从 ：51.2分钟—>9.144分钟—>4.071分钟—>1.456分钟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
实测在如此配置的情况下，indexonlyscan优先!
所谓的 IndexOnlyScan，表示只在索引取数据，不用再定位物理位置后再取数据。性能最快。

通过以上的配置，重启下服务器。再执行查询计划时可以看到：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
test=# explain select count(id) from tbl_time1 where id > 0;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=118803211.23..118803211.24 rows=1 width=4)
   ->  Index Only Scan using tbl_time1_pkey on tbl_time1  (cost=0.57..118719877.89 rows=33333333 width=4)
         Index Cond: (id > 0)
(3 rows)
Time: 16.033 ms
这时变成了：Index Only Scan 了。
不过耗时还是需要 
test=# select count(id) from tbl_time1 where id > 0;
   count  
-----------
100000000
(1 row)
 
Time: 87501.151 ms
约：1.456分钟

估算表大小

SELECT reltuples FROM pg_class WHERE relname = 'tb_name';

并发方式建立索引

当Postgres建立你的索引的时候， 和其他数据库一样, 在建立索引的时候是会锁表的. 对于小数据量来说没什么关系， 但是通常可能是我们对一个大数据量的表加索引, 这意味着要获得性能改进应用必须收到停机一段时间. 至少那一张表会受影响. Postgres有能力在创建索引的时候不锁表, 通过使用 CREATE INDEX CONCURRENTLY , 例如:

CREATE INDEX CONCURRENTLY idx_salary ON employees(last_name, salary);

当你的索引比你聪明的时候

在所有索引没有被Postgres使用的情况, 大多数情况下你应该相信Postgres, 例如当你查询的结果占所有数据的大部分时候, 它可能不使用索引，因为只扫描全表一次最简单,而不是使用索引做额外的查找.