自行测试的1亿条数据中PostgreSQL性能
Contents
插入一亿条数据
test=# insert into tbl_time1 select generate_series(1,100000000),clock_timestamp(),now();
INSERT 0 100000000
Time: 525833.218 ms
约:8.7分钟
COUNT，没有索引，1亿条数据。
test=# select count(1) from tbl_time1;
count
-----------
100000000
(1 row)
Time: 3070658.058 ms
约:51.2分钟
添加主键索引耗时
test=# alter table tbl_time1 add primary key (id);
ALTER TABLE
Time: 981276.804 ms
约：16.4分钟
再 explain 一下看看
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 的条件的差别
这个有 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 扫描。
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分钟
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，表示只在索引取数据，不用再定位物理位置后再取数据。性能最快。
通过以上的配置，重启下服务器。再执行查询计划时可以看到：
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, 例如当你查询的结果占所有数据的大部分时候, 它可能不使用索引，因为只扫描全表一次最简单,而不是使用索引做额外的查找.