PostgreSQL 是一個流行的開源關聯式資料庫管理系統，提供多種索引類型以最佳化查詢效能並提高資料擷取效率。在本文中，我們將學習如何在 PostgreSQL 中建立不同類型的索引。在可能的情況下，我們將使用 DML 陳述式以及 Navicat for PostgreSQL 16 在免費的 「dvdrental」範例資料庫中 應用索引。
1. B-Tree 索引：
B-Tree 索引是 PostgreSQL 的預設索引類型，適用於多種資料類型，包括文字、數字和時間戳記。它以平衡的樹結構組織資料，能促進高效的範圍查詢和相等搜尋。讓我們在「payment」資料表中的「customer_id」欄位上建立一個 B-Tree 索引：
CREATE INDEX btree_customer_id_idx ON payment(customer_id);
在 Navicat 中，你可以在資料表設計器的「索引」索引標籤上找到索引。若要建立上述的索引，我們需要在「名稱」欄位中輸入「btree_customer_id_idx」，在「欄位」中選擇「customer_id」，然後從「索引方法」下拉式清單中選取「B-Tree」：
以下是填入了上述所有欄位的 btree_customer_id_idx 索引：
按一下「儲存」按鈕將建立索引。
2. Hash 索引：
Hash 索引在進行相等性檢查時效果最佳，但對於範圍查詢則不太有效。他們使用 hash 函式將鍵對應到索引項目。以下是如何在「inventory」資料表的「film_id」欄位上建立 Hash 索引，首先使用 DML 陳述式：
CREATE INDEX hash_film_id_idx ON inventory USING HASH(film_id);
現在使用 Navicat 建立索引：
3. GiST 索引：
通用搜尋樹（Generalized Search Trees，GiST）索引支援各種資料類型和複雜查詢，使其適用於全文搜尋和幾何資料類型等應用程式。
以下是在 geometry 欄位上建立 GiST 索引的範例：
CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);
4. SP-GiST 索引：
空間分區廣義搜尋樹（Space-Partitioned Generalized Search Tree，SP-GiST）索引適用於具有多維或層級結構的資料類型。它們可以高效地對非平衡樹進行索引。
以下是在 tsvector 欄位上建立 SP-GiST 索引的範例：
CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);
5. GIN 索引：
通用倒排索引（Generalized Inverted Index，GIN）非常適合全文搜尋、陣列類型和複合資料類型等情況。它們對於具有多個鍵或元件的資料類型非常有效。我們在「film」資料表的「title」欄位上建立一個GIN索引，以用於全文搜尋：
CREATE INDEX gin_title_idx ON film USING gin(to_tsvector('english', title));
以下是 Navicat 中「film」資料表的「索引」索引標籤，新增了 gin_title_idx 索引：
6. BRIN 索引：
B區塊範圍索引（Block Range Index，BRIN）適用於具有排序資料的大型資料表，因為它索引資料區塊的範圍而不是單一行。它對於相鄰值之間具有相關性的欄位來說非常有效。以下是在「rental」資料表中的「rental_date」欄位上建立 BRIN 索引的方法：
CREATE INDEX brin_rental_date_idx ON rental USING brin(rental_date);
以下是 Navicat 中的 brin_rental_date_idx 索引：
總結
PostgreSQL 提供了多種索引類型，以滿足不同的資料類型和查詢需求。了解每種索引類型的特點有助於資料庫管理員和開發人員在最佳化資料庫效能時做出明智的決策。同時，使用 Navicat for PostgreSQL 16 等工具可以讓索引的使用變得更加容易。