本文比较了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）：索引没有多版本也没有足以判断可见性的信息，在查询时会回表判断页的可见性，在发现页非全部可见时扫描元组确定。