PostgreSQL之索引(三)索引分析

EXPLAIN

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
这里 option可以是：

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

　这个命令显示PostgreSQL计划器为提供的语句所生成的执行计划。该执行计划会显示将怎样扫描语句中引用的表 — 普通的顺序扫描、索引扫描等等 — 以及在引用多个表时使用何种连接算法来把来自每个输入表的行连接在一起。

　　显示中最重要的部分是估计出的语句执行代价，它是计划器对于该语句要运行多久的猜测（以任意的代价单位度量，但是习惯上表示取磁盘页面的次数）。事实上会显示两个数字：在第一行能被返回前的启动代价，以及返回所有行的总代价。对于大部分查询来说总代价是最重要的，但是在一些情景中（如EXISTS中的子查询），计划器将选择更小的启动代价来代替最小的总代价（因为执行器将在得到一行后停止）。此外，如果你用一个LIMIT子句限制返回行的数量，计划器会在终端代价之间做出适当的插值来估计到底哪个计划是真正代价最低的。

　　ANALYZE选项导致该语句被实际执行，而不仅仅是被计划。那么实际的运行时间统计会被显示出来，包括在每个计划结点上花费的总时间（以毫秒计）以及它实际返回的行数。这对观察计划器的估计是否与实际相近很有用。

-- capture_img 是分区表
EXPLAIN ANALYZE 
SELECT * 
FROM
    capture_img  
WHERE
    id > 1612108800000000 
    AND id < 1612281600000000
    AND camera_id IN ( 753381361 :: VARCHAR, 753380579 :: VARCHAR ) ORDER BY
    capture_time 
    LIMIT 10;
-------------------
Limit  (cost=344.81..344.84 rows=10 width=1503) (actual time=0.600..0.604 rows=10 loops=1)
  ->  Sort  (cost=344.81..345.02 rows=84 width=1503) (actual time=0.599..0.600 rows=10 loops=1)
        Sort Key: capture_img_20210201.capture_time
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Append  (cost=9.15..343.00 rows=84 width=1503) (actual time=0.075..0.472 rows=142 loops=1)
              ->  Bitmap Heap Scan on capture_img_20210201  (cost=9.15..163.55 rows=40 width=1501) (actual time=0.074..0.242 rows=69 loops=1)
                    Recheck Cond: ((camera_id)::text = ANY ('{753381361,753380579}'::text[]))
                    Filter: ((id > '1612108800000000'::bigint) AND (id < '1612281600000000'::bigint))
                    Heap Blocks: exact=69
                    ->  Bitmap Index Scan on capture_img_20210201_camera_id_idx  (cost=0.00..9.14 rows=40 width=0) (actual time=0.055..0.056 rows=69 loops=1)
                          Index Cond: ((camera_id)::text = ANY ('{753381361,753380579}'::text[]))
              ->  Bitmap Heap Scan on capture_img_20210202  (cost=9.18..179.03 rows=44 width=1506) (actual time=0.061..0.212 rows=73 loops=1)
                    Recheck Cond: ((camera_id)::text = ANY ('{753381361,753380579}'::text[]))
                    Filter: ((id > '1612108800000000'::bigint) AND (id < '1612281600000000'::bigint))
                    Heap Blocks: exact=73
                    ->  Bitmap Index Scan on capture_img_20210202_camera_id_idx  (cost=0.00..9.17 rows=44 width=0) (actual time=0.045..0.045 rows=73 loops=1)
                          Index Cond: ((camera_id)::text = ANY ('{753381361,753380579}'::text[]))
Planning Time: 2.118 ms
Execution Time: 0.721 ms

索引分析

　　pg_stat_user_indexes：用户表上的索引

列	类型	描述
`relid`	`oid`	这个索引的基表的 OID
`indexrelid`	`oid`	这个索引的 OID
`schemaname`	`name`	这个索引所在的模式的名称
`relname`	`name`	这个索引的基表的名称
`indexrelname`	`name`	这个索引的名称
`idx_scan`	`bigint`	在这个索引上发起的索引扫描次数
`idx_tup_read`	`bigint`	在这个索引上由扫描返回的索引项数量
`idx_tup_fetch`	`bigint`	被使用这个索引的简单索引扫描取得的活着的表行数量

select * from pg_stat_user_indexes limit 2;
-----------
54790904    54793673    public    t_place_geo    t_place_geo_pkey    0    0    0
54790904    54793672    public    t_place_geo    index_polyline    276438    553299    0

　　pg_statio_user_indexes：显示与指定索引上的 I/O 有关的统计信息

列	类型	描述
`relid`	`oid`	这个索引的基表的 OID
`indexrelid`	`oid`	这个索引的 OID
`schemaname`	`name`	这个索引所在的模式的名称
`relname`	`name`	这个索引的基表的名称
`indexrelname`	`name`	这个索引的名称
`idx_blks_read`	`bigint`	从这个索引读取的磁盘块数
`idx_blks_hit`	`bigint`	在这个索引中的缓冲区命中数量

select * from pg_statio_user_indexes limit 2;
--------------
54790904    54793673    public    t_place_geo    t_place_geo_pkey    315    979
54790904    54793672    public    t_place_geo    index_polyline    1516    1656908