本文比较了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;

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

ITPub博客

Oracle vs PostgreSQL Develop（31） - Index Only Scan

最新文章