PostgreSQL 10 索引 - 理解简单的查询和成本模型

我们先建表,插入数据:

postgres=# CREATE TABLE t_test (id serial, name text);
CREATE TABLE
postgres=# INSERT INTO t_test (name) SELECT 'hans'
postgres-# FROM generate_series(1, 2000000);
INSERT 0 2000000
postgres=# INSERT INTO t_test (name) SELECT 'paul'
postgres-# FROM generate_series(1, 2000000);
INSERT 0 2000000

这样,我们有了400万行数据:

postgres=# SELECT name, count(*) FROM t_test GROUP BY 1;
 name |  count  
------+---------
 hans | 2000000
 paul | 2000000
(2 行记录)

我们执行一个简单的查询:

postgres=# \timing
启用计时功能.
postgres=# SELECT * FROM t_test WHERE id = 432332;
   id   | name 
--------+------
 432332 | hans
(1 行记录)

时间:111.051 ms

上面的例子,timing命令会告诉psql,显示查询的运行时间。注意,这不是实际的执行时间,而是由psql测量的时间。这个很短的查询,也应该考虑网络传输时间。

使用EXPLAIN

前面的例子,读400万行花了100多毫秒。从性能上看,这是一场灾难。可以使用EXPLAIN命令,看看哪里有问题。
explain

当你感觉某查询的性能不够好,可以使用EXPLAIN揭示性能问题。

postgres=# EXPLAIN SELECT * FROM t_test WHERE id = 432332;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Gather  (cost=1000.00..43455.43 rows=1 width=9)
   Workers Planned: 2
   ->  Parallel Seq Scan on t_test  (cost=0.00..42455.33 rows=1 width=9)
         Filter: (id = 432332)
(4 行记录)

时间:0.499 ms

在这个执行计划列表中,你看到了什么?PostgreSQL中,一个SQL语句的执行分四个阶段:

  • parser:检查语法错误和明显的问题
  • rewrite:系统负责的规则(视图和其他)
  • optimizer:如何以最有效的方式执行查询并制定计划
  • plan:由优化器提供,执行器用它产生结果

EXPLAIN的目的是看规划器准备如何高效地查询。在我的例子里,PostgreSQL使用并行的顺序扫描。这意味着两个workers会协作,共同处理过滤条件。通过聚合节点(gather node,从PostgreSQL 9.6开始支持,这是并行查询架构的一部分)将各部分结果联合到一起。你还可以看到PostgreSQL希望在计划的每个阶段返回多少行。
PostgreSQL 9.6中,并行workers的数量由表的大小决定。小表不会使用并行,因为开销很大。
也不是必须并行:

postgres=# SET max_parallel_workers_per_gather TO 0;
SET

当然,这样做只影响当前session。也可以修改postgresql.conf中的配置,但是,强烈建议不要这么做。

深入PostgreSQL成本模型

如果只使用一个CPU,执行计划看起来像这样的:

postgres=# EXPLAIN SELECT * FROM t_test WHERE id = 432332;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on t_test  (cost=0.00..71622.00 rows=1 width=9)
   Filter: (id = 432332)
(2 行记录)

PostgreSQL会使用该过滤条件,顺序读整个表。预计要花费71622 penalty point-这是一个抽象的概念,需要比较执行查询的不同方式。PostgreSQL会决定使用看起来成本最低的执行计划。那为什么是71622点呢?

postgres=# SELECT pg_relation_size('t_test') / 8192.0;
      ?column?      
--------------------
 21622.000000000000
(1 行记录)

pg_relation_size函数返回表占用空间的字节数。可以看到,该表由21622个块组成。根据这个成本模型,PostgreSQL会计算顺序读每个块的成本。
影响它的配置参数是:

postgres=# SHOW seq_page_cost;
 seq_page_cost 
---------------
 1
(1 行记录)

但是,从磁盘读每个块并不是我们做的全部。还要应用过滤条件,要把数据送给CPU。有两个参数说明这些成本:

postgres=# SHOW cpu_tuple_cost;
 cpu_tuple_cost 
----------------
 0.01
(1 行记录)

postgres=# SHOW cpu_operator_cost;
 cpu_operator_cost 
-------------------
 0.0025
(1 行记录)

所以,是这样计算的:

postgres=# SELECT 21622*1 + 4000000*0.01 + 4000000*0.0025;
  ?column?  
------------
 71622.0000
(1 行记录)

你已经看到了,这是计划的成本。成本由CPU部分和I/O部分组成,最后计算出一个数。还有,这个成本和实际执行没有关系,这只是一个估计。
当然,这个简明的例子还列出了一些参数。PostgreSQL也有一些针对索引相关操作的参数:

  • random_page_cost = 4:如果使用索引,会有很多随机I/O。对于经典的磁盘,随机读比顺序读更重要,所以,PostgreSQL会考虑它们。对于SSDs,随机读和顺序读没什么差别,所以,可以在postgresql.conf文件中设置random_page_cost = 1
  • cpu_index_tuple_cost = 0.005:如果使用索引,PostgreSQL也会考虑CPU成本

如果使用了并行查询,还有更多的成本参数:

  • parallel_tuple_cost = 0.1:从并行工作的一个进程传输到另一个进程的成本
  • parallel_setup_cost = 1000.0:启动一个工作进程的成本
  • min_parallel_relation_size = 8 MB:使用并行查询的表的最小大小,表越大,使用的CPU就越多。表的大小增加到3倍,才会再启动一个进程

部署简单的索引

启用很多进程扫描大表通常不是个好主意。
读整张表来查找一条记录,更不是个好主意。

postgres=# CREATE INDEX idx_id ON t_test (id);
CREATE INDEX
postgres=# \timing
启用计时功能.
postgres=# SELECT * FROM t_test WHERE id = 43242;
  id   | name 
-------+------
 43242 | hans
(1 行记录)

时间:1.621 ms

PostgreSQL使用Lehman-Yao的高并发b-tree建标准索引。Lehman-Yao允许你在同一时刻在同一个索引上执行很多操作(读和写)。
但是,索引不是免费的:

postgres=# \di+
                          关联列表
 架构模式 |  名称  | 类型 |  拥有者  | 数据表 | 大小  | 描述 
----------+--------+------+----------+--------+-------+------
 public   | idx_id | 索引 | postgres | t_test | 86 MB | 
(1 行记录)

我们的400万行记录的索引,吃掉了86MB磁盘空间。而且会导致写表变慢-因为索引是同步的。

输出使用排序

B-tree索引不只是可以用来找记录,排序的时候也能用:

postgres=# EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.74 rows=10 width=9)
   ->  Index Scan Backward using idx_id on t_test  (cost=0.43..125505.43 rows=4000000 width=9)
(2 行记录)

可以看到,索引能以正确的顺序返回数据,而不用扫描表。
除了ORDER BY,min和max函数也需要排序,索引也能提升他们的性能:

postgres=# explain SELECT min(id), max(id) FROM t_test;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.93..0.94 rows=1 width=8)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.43..0.46 rows=1 width=4)
           ->  Index Only Scan using idx_id on t_test  (cost=0.43..135505.43 rows=4000000 width=4)
                 Index Cond: (id IS NOT NULL)
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.43..0.46 rows=1 width=4)
           ->  Index Only Scan Backward using idx_id on t_test t_test_1  (cost=0.43..135505.43 rows=4000000 width=4)
                 Index Cond: (id IS NOT NULL)
(9 行记录)

PostgreSQL中,b-tree能以正常顺序读,也能以反序读。可以把b-tree看成一个排序列表。所以,自然地,最小值在前面,最大值在后面。

同时使用多个索引

PostgreSQL允许一个查询内使用多个索引。甚至一个索引可以被多次使用:

postgres=# explain SELECT * FROM t_test WHERE id = 30 OR id = 50;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Bitmap Heap Scan on t_test  (cost=8.88..16.85 rows=2 width=9)
   Recheck Cond: ((id = 30) OR (id = 50))
   ->  BitmapOr  (cost=8.88..8.88 rows=2 width=0)
         ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (id = 30)
         ->  Bitmap Index Scan on idx_id  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (id = 50)
(7 行记录)

位图扫描和位图索引不同。
PostgreSQL的位图扫描是,扫描第一个索引,收集包含数据的块列表。然后,下一个索引扫描块列表……本例中使用的OR,这些列表被统一成一个包含数据的大的块列表。使用这个列表,扫描表检索这些块。
问题是PostgreSQL检索了比需要的更多的数据。我们的例子要找两行,但是,位图扫描可能返回几个块。因此,执行器重新检查,过滤这些记录,来满足条件。
AND条件,或者AND和OR的混合条件,也使用位图扫描。不过,如果PostgreSQL看到AND条件,它不一定强迫自己使用位图扫描。PostgreSQL优化器会比较不同计划的成本。

高效地使用位图扫描

什么时候优化器会选择位图扫描呢?我觉得有两种用例:

  • 避免一次又一次地使用相同的块:假设你在找说某种语言的所有的人,比如会找出10%的人。扫描索引的话,表中的块可能被扫描多次,因为说不同语言的人可能保存在一个块内。使用位图扫描,可以确保每个块之被使用一次
  • 结合相对不好的条件:假设我们要找20-30岁之间有黄色T恤的人。可能20%-30%的人的年龄在20-30之间,15%的人有黄色T恤。顺序扫描表太昂贵了,PostgreSQL可能决定选择两个索引,这是因为最终的结果可能由1%的数据组成。扫描两个索引比读所有的数据更廉价

PostgreSQL 10.0,支持并行的位图堆扫描。

智能地使用索引

使用索引,并不总是能提高性能。
在深入之前,再回顾一下我们使用的数据结构-只有两个不同的名字和唯一的ID。

postgres=# \d t_test
                           数据表 "public.t_test"
 栏位 |  类型   | Collation | Nullable |              Default               
------+---------+-----------+----------+------------------------------------
 id   | integer |           | not null | nextval('t_test_id_seq'::regclass)
 name | text    |           |          | 
索引:
    "idx_id" btree (id)

目前,id列有索引。下一步,我们要查询name列,于是在name列也加一个索引。

postgres=# CREATE INDEX idx_name ON t_test (name);
CREATE INDEX
Time: 3191.722 ms (00:03.192)

现在看看是否正确地使用了索引:

postgres=# EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using idx_name on t_test  (cost=0.43..8.45 rows=1 width=9)
   Index Cond: (name = 'hans2'::text)
(2 行记录)

PostgreSQL决定使用索引。大多数用户期望这样。但是,查询条件是“hans2”,而hans2并不存在,查询计划完美地反映了这一点,rows=1,说明优化器只希望查询返回很小的子集。
表里没这一行,但是PostgreSQL不会估计0行,这是因为它会使随后的估计变得更加困难。

让我们看看,如果查询更多的数据会发生什么:

postgres=# EXPLAIN SELECT * FROM t_test
postgres-# WHERE name = 'hans' OR name = 'paul';
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on t_test  (cost=0.00..81622.00 rows=3000091 width=9)
   Filter: ((name = 'hans'::text) OR (name = 'paul'::text))
(2 行记录)

PostgreSQL采用了顺序扫描。为什么呢?系统为什么忽略了索引?理由是简单的:hans和paul组成了整个表(检查系统统计)。没有理由读索引以及整个表。
如果行的数量少,PostgreSQL再次考虑位图扫描和索引扫描:

postgres=# EXPLAIN SELECT * FROM t_test
WHERE name = 'hans2' OR name = 'paul2';
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Bitmap Heap Scan on t_test  (cost=8.88..12.89 rows=1 width=9)
   Recheck Cond: ((name = 'hans2'::text) OR (name = 'paul2'::text))
   ->  BitmapOr  (cost=8.88..8.88 rows=1 width=0)
         ->  Bitmap Index Scan on idx_name  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (name = 'hans2'::text)
         ->  Bitmap Index Scan on idx_name  (cost=0.00..4.44 rows=1 width=0)
               Index Cond: (name = 'paul2'::text)
(7 行记录)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值