本文比較了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）：索引沒有多版本也沒有足以判斷可見性的資訊，在查詢時會回表判斷頁的可見性，在發現頁非全部可見時掃描元組確定。

Oracle vs PostgreSQL Develop（31） - Index Only Scan

相關文章