Index是什麼

PostgreSQL如果要將資料從table中讀出，主要會有兩種方式:
1.Sequential scan:連續讀取整個table的檔案，然後從中解析裡面的資料。
2.Index scan:在Postgres的優化器覺得情況許可的時候，透過查詢Index(一個和主table分離的檔案，在建立的時候可以將指定的欄位值做成search key，將每個資料行的search key數值以及其在檔案系統中的相對位置記錄在一個像是b-tree之類能夠快速搜尋的資料結構中)來讀取table中一到多個block並解析裡面的資料。
使用何種方式較佳，主要取決於所查詢的資料在table中的占比。如果想要查詢的table很大，而Postgres的優化器根據系統中對於欄位數值分布的統計資料來預測，認為該次查詢只會回傳table的一小部分，選擇去做index scan，就只要做少數幾次的隨機block讀取即完成查詢動作，不需要讀取整個table的內容，前者花費的時間就會比後者少很多。但是如果需要讀取的比例大到一定程度，多個隨機I/O動作會有其效能上的消耗，導致一次讀取整個檔案(Sequential scan)會變成是比較有效率的做法。

Index管理相關指令

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [
ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [
ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]

PostgreSQL預設會對table的primary key建立unique index，或者可以透過指令來建立。
針對單一欄位建立index:
CREATE INDEX idx_post_category ON posts( category );
針對多個欄位建立index(代表排序時比較的優先順序):
CREATE INDEX idx_author_created_on ON posts( author, created_on );
PostgreSQL預設的index類型為b-tree index，也可以在創建的時候指定其他的index類型:
CREATE INDEX idx_post_created_on ON posts USING hash ( created_on ); // 以hash index類型來做

index可以透過DROP INDEX ${index名稱}來移除，使用REINDEX指令可以讓我們在必要時重新建立Index。

Bitmap index scan

Bitmap index scan是介於sequential scan(全表掃描)跟index scan(透過索引來找尋到資料行的位置，逐一讀取)的模式，方法是將所有index中找到符合條件的資料行他們所在的block給收集起來，排序之後做成bitmap之後一次掃描這些block。

index使用技巧

1.B-tree index的使用跟資料的順序有一些關係。由於在b-tree index中資料會以指定的順序排列，因此在做查詢的時候，如果使用建立index key的順序來輸出最終的結果，index scan也可以順便處理掉排序的部分。
2.如果有一個以上的欄位過濾條件在查詢一個table的過程中被使用，而這兩個欄位上面都剛好有做index，則Postgres可能會透過bitmap index scan的方式，將這些欄位對應index的bitmap生成後進行AND/OR操作之後對table做bitmap index scan，增加查詢的效能。
3.對於有使用foreign key的情形，針對child table的foreign key欄位如果有index，有機會讓join的執行時間縮短。

Covering index & index-only scan

PostgreSQL在create index的時候，還可以透過INCLUDE指令，將其他除了index seach key以外的欄位資料加入index entry裡面，在查詢的時候如果只用到這些欄位就可以直接從index上面讀取，作為index-only scan，比需要再次存取table以取得所需資料的index scan來得快，但方便性也反映在index的大小以及資料刪除/更新時對index套用變更的效能上。

Partial index

如果今天對於index欄位查出來的結果都會做特定的過濾條件，而且要被index的table相當大的時候，Postgres可以讓我們建立partial index，在建立index的時候可以設定只索引符合特定條件的資料行，讓index得以瘦身，降低空間占用以及查詢所需的時間。
例如create index idx_name on test_indexing(name) where name not in ('bob', 'alice');可以將表格中name欄位的值為bob或alice的資料行在做index的時候進行排除。

CLUSTER

如果針對某些table需要進行超過一定量特定順序的index scan或是特定順序的查詢後排序，CLUSTER這個指令搭配排序欄位的index可以增進這些動作的效能。
CLUSTER的動作會根據index key的順序對table在磁碟上的內容進行物理排序，也就是說在Sequential scan整個將table檔案讀出來的時候，每一個tuple的順序基本上都跟index所指定的一樣了，所以用index所指定的排序來輸出結果的時候就會比較輕鬆。如果是做特定範圍index scan的話，由於index search key相鄰的內容在經過CLUSTER過後，磁碟上的位置也會是相鄰的，所以讀取的時候可以降低隨機的I/O，甚至是可以讀取作業系統已經快取起來的資料分頁(page)，在查詢的性能上也是可以得到提升。
下指令的部分就是CLUSTER table USING ${index_name};