# Index: Key V.S. Non-Key Column

### 前置作業：

create `students` table with many columns and insert multiple dummy data

```bash
postgres=# create table students (
id serial primary key, 
g int,
firstname text, 
lastname text, 
middlename text,
address text,
bio text,
dob date,
id1 int,
id2 int,
id3 int,
id4 int,
id5 int,
id6 int,
id7 int,
id8 int,
id9 int
);
```

## 三種搜尋情況：

### 1\. 沒有index

預計的總執行時間`Execution Time:` 5350.434 ms

`Parallel Seq Scan`: 沒有index，所以需要全表搜尋，到Disk取出非常非常多Page

`Rows Removed by Filter`: 移除Page上不需要的rows，總共移除15166941

```bash
postgres=# explain analyze select id, g from students where g > 80 and g < 90 order by g desc;
                                                                 QUERY PLAN                         
                                         
----------------------------------------------------------------------------------------------------
-----------------------------------------
 Gather Merge  (cost=1471103.74..1877322.27 rows=3481630 width=8) (actual time=4838.572..5238.071 ro
ws=4499177 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=1470103.71..1474455.75 rows=1740815 width=8) (actual time=4785.631..4859.172 rows
=1499726 loops=3)
         Sort Key: g DESC
         Sort Method: external merge  Disk: 27288kB
         Worker 0:  Sort Method: external merge  Disk: 25432kB
         Worker 1:  Sort Method: external merge  Disk: 26760kB
         ->  Parallel Seq Scan on students  (cost=0.00..1265853.15 rows=1740815 width=8) (actual tim
e=67.680..4516.032 rows=1499726 loops=3)
               Filter: ((g > 80) AND (g < 90))
               Rows Removed by Filter: 15166941
 Planning Time: 1.963 ms
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 5.333 ms, Inlining 125.081 ms, Optimization 49.582 ms, Emission 26.353 ms, Tot
al 206.349 ms
 Execution Time: 5350.434 ms
```

### 2\. 有index

create index on `g` column

目的：filter and order by `g`

```bash
postgres=# create index g_idx on students(g);
```

預計總執行時間`Execution Time`: 5441.724 ms

`Index Scan Backward`: 因為index是有序排列，query指定`order by g desc`，找到資料後，index就會依照query降冪排列

搜尋方式：

* 依照`g_idx` btree，找到80這個entry point，取`g > 80 AND g < 95` 的資料
    
* 找到符合的資料，再去Heap取出資料，取出後刪除不符合條件的資料
    

結果：跟以上沒有對`g`加上index的query，預計執行時間差不多，為什麼？

因為select 當中包含`id`，`id`存在兩個地方：

* 另一個index結構中，因為`id`是primary key
    
* Heap中
    

所以這個query仍然要到Heap中去取出`id`資料，多了這一道執行程序，執行速度就變慢了

```bash
postgres=# explain analyze select id, g from students where g > 80 and g < 95 order by g desc;

QUERY PLAN                         
                                         
----------------------------------------------------------------------------------------------
Index Scan Backward using g_idx on students (cost=0.89..1777645.78 rows=7781630 width=8) (actual time=4675.321..5019.178 rows
=7256890 loops=1)
  Index Cond: ((g > 80) AND (g < 95))
Planning Time: 1.049 ms
......
Execution Time: 5172.569 ms
```

正常情況下，在資料庫有巨量資料時，不會一次這麼多，通常會分批取

```bash
postgres=# explain analyze select id, g from students where g > 85 and g < 95 order by g desc limit 1000;
                                                                  QUERY PLAN                        
                                          
----------------------------------------------------------------------------------------------------
------------------------------------------
 Limit  (cost=0.56..2085.56 rows=1000 width=8) (actual time=0.084..8.454 rows=1000 loops=1)
   Buffers: shared hit=31 read=10
    ->  Index Scan Backward using g_idx on students  (cost=0.56..8910002.51 rows=4273389 width=8) (ac
tual time=0.081..8.326 rows=1000 loops=1)
         Index Cond: ((g > 85) AND (g < 95))
         Buffers: shared hit=31 read=10
 Planning:
    Buffers: shared hit=17
 Planning Time: 0.564 ms
 Execution Time: 8.580 ms
```

除了分批取資料，大大縮減query執行時間，另一個縮減執行時間的是cache機制

`Buffers: shared hit`: 資料已經被存在電腦的RAM中，所以不需要再去Disk找

`Buffers: shared read`: 要再去Disk取出的資料，取出就會存在RAM中，下次再取相同資料時，就不需要再去Disk取資料，query執行速度會更快

### 3\. Pure Index Only Scan: index + non-key column

Remove the previous index and create an index with a non-key column

```bash
postgres=# drop index g_idx;
postgres=# create index g_idx on students(g) include(id);
```

```bash
postgres=# explain (analyze, buffers) select id, g from students where g > 80 and g < 95 order by g desc;
                                                                       QUERY PLAN                   
                                                     
----------------------------------------------------------------------------------------------------
-----------------------------------------------------
Index Only Scan Backward using g_idx on students  (cost=0.56..14618271.76 rows=4245890 width=
8) (actual time=1.964..4069.781 rows=4471785 loops=1)
         Index Cond: ((g > 80) AND (g < 95))
         Heap Fetches: 0
         Buffers: shared hit=988 read=391892 written=20
 Planning:
   Buffers: shared hit=20
 Planning Time: 0.174 ms
 ......
 Execution Time: 4137.581 ms
```

### 結論：

* 資料量越大，越需要善用non-key index
    
* index包含越多non-key column，需要佔用的memory就越大
    
* 當memory放不下時，就會被放到Disk，此時若要執行query，執行前還是需要去Disk取出被存放的index
    

### 其他：

* `vacuum` 不是SQL語法，是PostgreSQL command line，是用來維護資料庫的指令，
    
* The `VACUUM` command in PostgreSQL is used to reclaim storage occupied by dead tuples. It's a maintenance operation that helps optimize the database by cleaning up and potentially recovering disk space.
    

```bash
vacuum (verbose) students;
vacuum (analyze, verbose, full) students;
```

```bash
vacuum (analyze, verbose, full);
```

### Reference:

Udemy Course: Fundamentals of Database Engineering 25