1 引言

PostgreSQL是一個功能強大、靈活且可擴充的關系型資料庫管理系統。它支援許多進階資料類型，如數組、JSON和全文搜尋等。要在這些複雜資料類型上進行高效搜尋，PostgreSQL提供了 GIN（Generalized Inverted Index，廣義反向索引）的特殊索引類型。本文将介紹GIN索引的基本概念、使用場景和建立方法。

2 GIN索引概述

GIN索引是為了加速包含多個元件的資料類型的搜尋而設計的。與傳統的B-tree索引不同，GIN索引可以在複雜數組或全文搜尋查詢中提供更快的查詢性能。GIN索引适用于查詢涉及包含、包含于或重疊運算符的情況。

廣義反向索引的工作原理是将複雜資料類型的每個元件分開存儲，這樣在查詢時可以迅速找到所需的元件。這種索引結構使得查詢複雜資料類型變得更快，因為隻需檢查所需元件，而不是整個資料結構。

3 使用場景

GIN索引适用于以下資料類型和操作：

數組: 當在數組元素上使用@>(包含)、<@(包含于)或&&(重疊)等運算符時，GIN索引可以提高查詢性能。
全文搜尋: PostgreSQL提供了全文搜尋功能，可以在大量文本資料中高效搜尋關鍵字。使用@@運算符進行全文搜尋時，GIN索引可以加速查詢。
JSONB: PostgreSQL支援JSONB資料類型，用于存儲和操作JSON資料。當使用@>(包含)、<@(包含于)或?(存在)等JSONB運算符時，GIN索引可以提高查詢性能。

4 建立GIN索引

要在PostgreSQL中建立一個GIN索引，可以使用CREATE INDEX指令，并指定USING gin。以下是幾個建立GIN索引的示例：

4.1 數組

GIN 索引在數組資料類型上的應用主要是為了加速包含（@>）、被包含（<@）以及重疊（&&）等操作。下面的示例将展示如何在數組資料類型上建立 GIN 索引。

假設我們有一個名為 products 的表，其中包含一個名為 tags 的數組列，用于存儲産品的标簽。我們可以為該列建立一個 GIN 索引，以加速基于标簽的查詢。

首先，建立一個 products 表并添加一些示例資料：

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  tags TEXT[] NOT NULL
);

INSERT INTO products (name, tags)
VALUES ('Product A', ARRAY['tag1', 'tag2', 'tag3']),
       ('Product B', ARRAY['tag2', 'tag3', 'tag4']),
       ('Product C', ARRAY['tag1', 'tag3', 'tag5']);

接下來，為 tags 列建立 GIN 索引：

CREATE INDEX products_tags_gin ON products USING gin(tags);

現在，我們可以使用包含（@>）、被包含（<@）以及重疊（&&）操作符來查詢 tags 列，GIN 索引将提高這些查詢的性能。

例如，我們可以查詢包含 'tag1' 和 'tag3' 的所有産品：

SELECT * FROM products WHERE tags @> ARRAY['tag1', 'tag3'];

或者，我們可以查詢具有至少一個與 ['tag2', 'tag4'] 重疊的标簽的産品：

SELECT * FROM products WHERE tags && ARRAY['tag2', 'tag4'];

在這些情況下，GIN 索引将加速查詢性能，進而提高基于數組資料類型的搜尋效率。

4.2 全文搜尋

PostgreSQL 提供了全文搜尋功能，可以在大量文本資料中高效搜尋關鍵字。使用 @@ 運算符進行全文搜尋時，GIN 索引可以加速查詢。下面的示例将展示如何在全文搜尋功能上建立 GIN 索引。

假設我們有一個名為 articles 的表，其中包含一個名為 title 的 VARCHAR 類型列和一個名為 content 的 TEXT 類型列，用于存儲文章的标題和内容。

首先，建立一個 articles 表并添加一些示例資料：

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL
);

INSERT INTO articles (title, content)
VALUES ('Title A', 'This is the content of article A.'),
       ('Title B', 'This is the content of article B.'),
       ('Title C', 'This is the content of article C.');

接下來，為了加速全文搜尋查詢，我們需要建立一個名為 content_vector 的全文搜尋向量列，然後使用 to_tsvector 函數為每篇文章生成全文搜尋向量。

ALTER TABLE articles ADD COLUMN content_vector tsvector;
UPDATE articles SET content_vector = to_tsvector('english', content);
// to_tsvector 函數通常以兩個參數調用：配置參數和輸入文本。配置參數用于指定分詞和詞彙規範化的規則，通常為語言名稱。

現在，我們可以為 content_vector 列建立一個 GIN 索引：

CREATE INDEX articles_content_vector_gin ON articles USING gin(content_vector);

有了 GIN 索引，我們可以使用 @@ 運算符進行高效的全文搜尋。例如，我們可以查詢包含詞彙 “content” 的所有文章：

SELECT title FROM articles WHERE content_vector @@ to_tsquery('english', 'content');

在這種情況下，GIN 索引将顯著提高全文搜尋的性能，使得在大量文本資料中查找關鍵字更加高效。為了確定資料一緻性，當插入或更新 articles 表時，還需要更新 content_vector 列。可以通過觸發器或其他邏輯來實作這一點。

4.3 在JSONB資料類型上建立GIN索引的示例

JSONB資料類型是PostgreSQL中用于存儲和操作JSON資料的二進制格式。JSONB列可以使用GIN索引來加速基于鍵值對的搜尋、包含(@>)和被包含(<@)操作。以下示例将展示如何在JSONB資料類型上建立GIN索引。

假設我們有一個名為profiles的表，其中包含一個名為data的JSONB列，用于存儲使用者配置檔案中的資訊。我們可以為該列建立一個GIN索引，以加速基于JSON鍵值對的查詢。

首先，建立一個profiles表并添加一些示例資料：

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

INSERT INTO profiles (data)
VALUES ('{"name": "Alice", "age": 25, "hobbies": ["reading", "sports"]}'::jsonb),
       ('{"name": "Bob", "age": 30, "hobbies": ["movies", "sports"]}'::jsonb),
       ('{"name": "Charlie", "age": 22, "hobbies": ["music", "sports"]}'::jsonb);

接下來，為data列建立GIN索引：

CREATE INDEX profiles_data_gin ON profiles USING gin(data);

現在，我們可以使用包含(@>)和被包含(<@)操作符來查詢data列，GIN索引将提高這些查詢的性能。

例如，我們可以查詢年齡為30的使用者：

SELECT * FROM profiles WHERE data @> '{"age": 30}'::jsonb;

或者，我們可以查詢愛好中包含"sports"的所有使用者：

SELECT * FROM profiles WHERE data -> 'hobbies' @> '["sports"]'::jsonb;

在這些情況下，GIN索引将加速查詢性能，進而提高基于JSONB資料類型的搜尋效率。

5. GIN索引的優化和維護

GIN索引雖然可以加速複雜資料類型的查詢，但為了確定其性能和空間占用在可接受範圍内，我們需要對GIN索引進行優化和維護。本章将介紹如何優化和維護GIN索引。

5.1 GIN索引的性能優化選項

PostgreSQL提供了一些GIN索引的性能優化選項，可以根據具體需求進行調整：

fastupdate：預設情況下，GIN索引會開啟fastupdate選項，這意味着新插入或更新的資料首先被存儲在一個單獨的緩沖區中，而不是直接寫入索引。這可以提高寫入性能，但在某些情況下，可能會導緻查詢性能降低。如果查詢性能非常重要，可以考慮關閉fastupdate選項：

CREATE INDEX example_gin_index ON table_name USING gin(column_name) WITH (fastupdate = off);

gin_pending_list_limit：這個參數用于控制fastupdate緩沖區的大小。當緩沖區達到該限制時，資料将被合并到GIN索引中。增大此參數可以減少合并操作的次數，進而提高寫入性能，但可能會導緻更多的記憶體消耗。預設值為64KB。

5.2 GIN索引的空間占用和維護

盡管 GIN 索引在搜尋複雜資料類型方面具有顯著優勢，但它們也有一定的缺點，例如空間占用和維護成本。本節将介紹 GIN 索引的空間占用和維護政策。

空間占用

GIN 索引通常比其他索引類型（如 B-tree）占用更多的磁盤空間。這是因為 GIN 索引需要存儲多個鍵值對及其對應的記錄指針。在某些情況下，GIN 索引的大小可能會成為一個問題，尤其是在具有大量資料和高索引選擇性的表上。是以，在建立 GIN 索引之前，請權衡性能優勢與空間占用之間的關系。

維護成本

GIN 索引的維護成本主要表現在插入和更新操作上。由于 GIN 索引需要在每次插入或更新資料時更新鍵值對，是以會增加寫入性能的開銷。但是，PostgreSQL 通過一種名為 "fast update" 的技術來降低 GIN 索引的更新成本。"fast update" 将對索引的更改暫時存儲在一個單獨的緩沖區中，然後在背景批量應用這些更改。

盡管 "fast update" 能夠減輕 GIN 索引的維護成本，但在某些情況下，它可能會導緻索引變得 "膨脹"，進而占用更多的磁盤空間。要解決這個問題，你可以定期運作 VACUUM 或 ANALYZE 指令來整理索引并釋放未使用的磁盤空間。

VACUUM VERBOSE profiles;

或者：

ANALYZE VERBOSE profiles;

這些指令将整理 GIN 索引，釋放未使用的空間，并更新統計資訊以幫助查詢優化器生成更有效的查詢計劃。

5.3 VACUUM 和 ANALYZE 指令的使用

當使用 GIN 索引時，定期運作 VACUUM 和 ANALYZE 指令是至關重要的，以保持索引的效率和健康。本節将介紹這兩個指令的作用以及如何使用它們。

VACUUM 指令

VACUUM 指令用于回收已删除的行占用的存儲空間并整理表及其索引。在 PostgreSQL 中，删除的行不會立即從磁盤中删除，而是在一段時間後通過 VACUUM 程序來清理。此外，當資料更新或删除時，索引可能會産生 "膨脹"，導緻占用更多的磁盤空間。定期運作 VACUUM 指令可以幫助解決這些問題。

要對整個資料庫運作 VACUUM，可以執行以下指令：

VACUUM;

要對特定表運作 VACUUM，可以執行以下指令：

VACUUM table_name;

例如：

VACUUM profiles;

如果你希望檢視 VACUUM 過程的詳細資訊，可以使用 VERBOSE 選項：

VACUUM VERBOSE table_name;

ANALYZE 指令

ANALYZE 指令用于更新 PostgreSQL 查詢優化器所需的統計資訊。這些統計資訊有助于查詢優化器生成更有效的查詢計劃，進而提高查詢性能。當資料發生變化時，統計資訊可能會過時，是以定期運作 ANALYZE 指令是很有必要的。

要對整個資料庫運作 ANALYZE，可以執行以下指令：

ANALYZE;

要對特定表運作 ANALYZE，可以執行以下指令：

ANALYZE table_name;

例如：

ANALYZE profiles;

如果你希望檢視 ANALYZE 過程的詳細資訊，可以使用 VERBOSE 選項：

ANALYZE VERBOSE table_name;

是以，在使用 GIN 索引時，需要注意其空間占用和維護成本。通過定期整理索引并監控磁盤空間使用情況，可以確定 GIN 索引在提供高性能搜尋的同時保持可管理和高效。

6 GIN索引的局限性

盡管GIN索引在處理複雜資料類型的搜尋方面具有優勢，但它們也存在一定的局限性。在決定是否使用GIN索引之前，了解這些局限性非常重要。

不支援所有資料類型

GIN索引适用于特定的資料類型，如數組、tsvector（全文搜尋向量）和JSONB。對于其他資料類型，例如整數、浮點數和日期等，使用B-tree或其他适當的索引類型可能更加合适。是以，在選擇GIN索引之前，請確定資料類型适用于GIN索引。

不适用于等值查詢

GIN索引主要用于加速包含、被包含和重疊等查詢操作。然而，對于等值查詢，GIN索引并不是最佳選擇。在這種情況下，通常建議使用B-tree索引。

插入和更新性能的影響

由于GIN索引需要在每次插入或更新資料時更新鍵值對，是以會增加寫入性能的開銷。雖然PostgreSQL提供了“快速更新”技術來降低GIN索引的更新成本，但仍然需要關注寫入性能。對于需要頻繁插入和更新的表，可能需要權衡GIN索引帶來的查詢性能優勢和寫入性能的影響。

空間占用

GIN索引通常比其他索引類型占用更多的磁盤空間，因為它們需要存儲多個鍵值對及其對應的記錄指針。在具有大量資料和高索引選擇性的表上，GIN索引的大小可能會成為一個問題。是以，在建立GIN索引之前，請權衡性能優勢與空間占用之間的關系。

7. 結論

PostgreSQL的GIN（Generalized Inverted Index）索引為複雜資料類型的搜尋帶來了顯著的性能優勢。通過使用GIN索引，可以高效地處理數組、全文搜尋和JSONB資料類型的查詢，進而滿足各種複雜查詢場景的需求。

然而，在使用GIN索引時，需要關注其局限性，如不支援所有資料類型、不适用于等值查詢、插入和更新性能的影響以及空間占用。在選擇GIN索引之前，確定資料類型和查詢模式适用，并權衡性能優勢與空間占用之間的關系。

總之，了解GIN索引的工作原理、使用場景以及局限性對于充分利用PostgreSQL中的複雜資料類型非常重要。在正确的情況下使用GIN索引可以大大提高查詢性能，進而使你的資料庫應用更加高效和可擴充。

PostgreSQL GIN 索引: 高效搜尋複雜資料類型

1 引言

2 GIN索引概述

3 使用場景

4 建立GIN索引

4.1 數組

4.2 全文搜尋

4.3 在JSONB資料類型上建立GIN索引的示例

5. GIN索引的優化和維護

5.1 GIN索引的性能優化選項

5.2 GIN索引的空間占用和維護

空間占用

維護成本

5.3 VACUUM 和 ANALYZE 指令的使用

VACUUM 指令

ANALYZE 指令

6 GIN索引的局限性

不支援所有資料類型

不适用于等值查詢

插入和更新性能的影響

空間占用

7. 結論

繼續閱讀

學習筆記226—Python Numpy之Nan和Inf處理

C#連接配接postgresql資料庫

10.4

HYSBZ 3672 購票

關于 java 轉碼 GBK -> UTF-8

postgresql替換換行符

查詢資料庫中存在的回車換行符

Django入門：第一個Django應用

python中清單、元組、集合、字典的基本操作

JavaScript中的資料類型轉換 NaN一、判斷資料類型的兩種方法：二、轉換為數字類型（number）：三、轉換為字元串類型（string）：四、轉換為布爾類型（boolean）：

postgresql 關于GBK和utf-8字元集問題

資料庫MySQL-2-資料類型、SQL語言、DDL、DML、DQL、TCL、DCLMySQL資料類型資料庫中資料的使用

PostgreSQL t_bits計算方法

PythonNumber詳解：探索資料類型與數值計算的奇妙世界#python#python程式設計

為什麼要選擇UniDAC