本文比较了Oracle和PG在执行Index Only Scan上的异同。 众所周知，Index Only Scan在count或者在查询的投影列中只有索引列时会用到，但由于两者在MVCC等方面的实现方式不同，Oracle和PG行为表现会不太一样。 **Oracle** 构造测试数据，测试脚本: ``` -- Oracle set timing on drop sequence seq_test; create sequence seq_test start with 1; drop table t1 purge; create table t1(id int not null,c1 varchar2(30) not null,c2 char(1) not null); insert into t1 select seq_test.nextval,seq_test.nextval||'t1','x' from dual; declare begin for i in 1..24 loop insert into t1 select seq_test.nextval,seq_test.nextval||'t1','x' from t1; commit; end loop; end; / create unique index idx_t1_id on t1(id); create index idx_t1_c2 on t1(c2); ANALYZE TABLE t1 COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES; set autotrace on select count(*) from t1; -- 更新一部分数据，重新查询 update t1 set c1 = seq_test.nextval||'x' where rownum < 10000; select count(*) from t1; ``` 执行结果： ``` SQL> drop sequence seq_test; 序列已删除。 已用时间: 00: 00: 00.00 SQL> create sequence seq_test start with 1; 序列已创建。 已用时间: 00: 00: 00.00 SQL> SQL> drop table t1 purge; 表已删除。 已用时间: 00: 00: 02.26 SQL> create table t1(id int not null,c1 varchar2(30) not null,c2 char(1) not null); 表已创建。 已用时间: 00: 00: 00.01 SQL> SQL> insert into t1 select seq_test.nextval,seq_test.nextval||'t1','x' from dual; 已创建 1 行。 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 1006770343 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | T1 | | | | | 2 | SEQUENCE | SEQ_TEST | | | | | 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 49 recursive calls 61 db block gets 79 consistent gets 0 physical reads 7996 redo size 879 bytes sent via SQL*Net to client 995 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> declare 2 begin 3 for i in 1..24 loop 4 insert into t1 select seq_test.nextval,seq_test.nextval||'t1','x' 5 from t1; 6 commit; 7 end loop; 8 end; 9 / PL/SQL 过程已成功完成。 已用时间: 00: 01: 36.38 SQL> SQL> create unique index idx_t1_id on t1(id); 索引已创建。 已用时间: 00: 00: 26.51 SQL> create index idx_t1_c2 on t1(c2); 索引已创建。 已用时间: 00: 00: 25.91 SQL> SQL> ANALYZE TABLE t1 COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES; 表已分析。 已用时间: 00: 00: 32.59 SQL> SQL> set autotrace on SQL> SQL> select count(*) from t1; COUNT(*) ---------- 16777216 已用时间: 00: 00: 00.35 执行计划 ---------------------------------------------------------- Plan hash value: 1106749630 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8341 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T1_C2 | 16M| 8341 (2)| 00:00:01 | --------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 30477 consistent gets 1 physical reads 0 redo size 553 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> ``` 如期望的，Oracle查询时会走INDEX_FFS。 这里要注意的是Oracle的index不会存储null，如果字段上面没有not null约束，那么执行计划不会走INDEX_FFS。 更新一部分数据，再次执行查询： ``` SQL> update t1 set c1 = seq_test.nextval||'x' where rownum < 10000; 已更新 9999 行。 已用时间: 00: 00: 00.13 SQL> select count(*) from t1; COUNT(*) ---------- 16777216 已用时间: 00: 00: 00.45 执行计划 ---------------------------------------------------------- Plan hash value: 1106749630 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8341 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T1_C2 | 16M| 8341 (2)| 00:00:01 | --------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 30477 consistent gets 0 physical reads 0 redo size 553 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> ``` 执行计划和统计信息没有变化（PG会有变化）。 **PostgreSQL** 在PostgreSQL中，NULL会存储在索引中，因此创建表时不需要添加not null约束。 测试脚本： ``` \timing on drop table t1; create table t1(id int,c1 varchar(30),c2 char(1)); insert into t1 select x,x||'t1','x' from generate_series(1,16777216) as x; analyze t1; create unique index idx_t1_id on t1(id); create index idx_t1_c2 on t1(c2); set max_parallel_workers_per_gather=0; explain (analyze,buffers) select count(*) from t1; explain (analyze,buffers) select count(*) from t1; set enable_seqscan=off; set enable_bitmapscan=off; explain (analyze,buffers) select count(*) from t1; -- 更新一部分数据，重新查询 update t1 set c1 = id||'y' where id < 10000; explain (analyze,buffers) select count(*) from t1; ``` 执行结果： ``` [local]:5014 pg14@testdb=# drop table t1; 2022-04-19 14:55:43.674 CST [3343] ERROR: table "t1" does not exist 2022-04-19 14:55:43.674 CST [3343] STATEMENT: drop table t1; ERROR: table "t1" does not exist Time: 0.569 ms [local]:5014 pg14@testdb=# create table t1(id int,c1 varchar(30),c2 char(1)); CREATE TABLE Time: 33.169 ms [local]:5014 pg14@testdb=# [local]:5014 pg14@testdb=# insert into t1 select x,x||'t1','x' from generate_series(1,16777216) as x; 2022-04-19 14:56:37.845 CST [3329] LOG: checkpoints are occurring too frequently (28 seconds apart) 2022-04-19 14:56:37.845 CST [3329] HINT: Consider increasing the configuration parameter "max_wal_size". INSERT 0 16777216 Time: 58149.965 ms (00:58.150) [local]:5014 pg14@testdb=# [local]:5014 pg14@testdb=# analyze t1; ANALYZE Time: 856.209 ms [local]:5014 pg14@testdb=# [local]:5014 pg14@testdb=# create unique index idx_t1_id on t1(id); CREATE INDEX Time: 23462.494 ms (00:23.462) [local]:5014 pg14@testdb=# create index idx_t1_c2 on t1(c2); CREATE INDEX Time: 16935.962 ms (00:16.936) [local]:5014 pg14@testdb=# [local]:5014 pg14@testdb=# explain (analyze,buffers) select count(*) from t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=306937.20..306937.21 rows=1 width=8) (actual time=4781.083..4781.084 rows=1 loops=1) Buffers: shared hit=97222 -> Seq Scan on t1 (cost=0.00..264994.16 rows=16777216 width=0) (actual time=0.010..2515.360 rows=16777216 loops=1) Buffers: shared hit=97222 Planning: Buffers: shared hit=5 Planning Time: 0.140 ms Execution Time: 4781.130 ms (8 rows) Time: 4782.040 ms (00:04.782) [local]:5014 pg14@testdb=# ``` 我们期望PG也可以跟Oracle那样通过FFS很快的返回结果，但实际上并没有，走了FTS。 禁用FTS和bitmaspscan，再次执行 ``` set enable_seqscan=off; set enable_bitmapscan=off; [local]:5014 pg14@testdb=# set enable_seqscan=off; SET Time: 0.252 ms [local]:5014 pg14@testdb=# set enable_bitmapscan=off; SET Time: 0.122 ms [local]:5014 pg14@testdb=# [local]:5014 pg14@testdb=# explain (analyze,buffers) select count(*) from t1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=350381.71..350381.72 rows=1 width=8) (actual time=6030.285..6030.286 rows=1 loops=1) Buffers: shared hit=3 read=14125 -> Index Only Scan using idx_t1_c2 on t1 (cost=0.43..308438.67 rows=16777216 width=0) (actual time=0.133..3759.549 rows=16777216 loops=1) Heap Fetches: 0 Buffers: shared hit=3 read=14125 Planning Time: 0.077 ms Execution Time: 6030.328 ms (7 rows) Time: 6030.925 ms (00:06.031) [local]:5014 pg14@testdb=# ``` 这次走了Index Only Scan，但时间比FTS要慢许多，性能比Oracle慢了一个数据级。 更新一部分数据，再次执行 ``` [local]:5014 pg14@testdb=# update t1 set c1 = id||'y' where id < 10000; UPDATE 9999 Time: 239.271 ms [local]:5014 pg14@testdb=# explain (analyze,buffers) select count(*) from t1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=350634.80..350634.81 rows=1 width=8) (actual time=5399.554..5399.555 rows=1 loops=1) Buffers: shared hit=14246 dirtied=9 -> Index Only Scan using idx_t1_c2 on t1 (cost=0.44..308668.46 rows=16786535 width=0) (actual time=4.185..3326.704 rows=16777216 loops=1) Heap Fetches: 20180 Buffers: shared hit=14246 dirtied=9 Planning Time: 0.072 ms Execution Time: 5399.591 ms (7 rows) Time: 5400.062 ms (00:05.400) [local]:5014 pg14@testdb=# ``` 相较于上一次，一是耗时变短了（缓存的作用），二是Heap Fetches从0变为20180，意味着除了索引扫描实际上还从数据表heap中提取了数据。 个中原因是Index only scan并不是真正意义的Index Only，在mvcc机制下，index only scan还是会回表（Heap Fetches）：索引没有多版本也没有足以判断可见性的信息，在查询时会回表判断页的可见性，在发现页非全部可见时扫描元组确定。