PostgreSQL中GIN索引的三種使用場景
更新時間：2025年07月21日 09:39:43 作者：岳麓丹楓001
本文主要介紹了PostgreSQL中GIN索引的三種使用場景,包括數(shù)組類型、JSONB類型和全文搜索,具有一定的參考價值,感興趣的可以了解一下
數(shù)組類型
當在 PostgreSQL 中使用 GIN 索引來處理數(shù)組類型時，可以通過以下示例來說明：
假設有一個表 books，其中有一個列 tags 存儲了書籍的標簽信息，使用數(shù)組類型來表示?，F(xiàn)在我們想要創(chuàng)建一個 GIN 索引來加快對標簽進行搜索的查詢。
首先，創(chuàng)建 books 表：
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(100),
tags TEXT[]
);
接下來，插入一些示例數(shù)據(jù)：
INSERT INTO books (title, tags) VALUES
('Book 1', ARRAY['fiction', 'adventure']),
('Book 2', ARRAY['science', 'fiction']),
('Book 3', ARRAY['romance', 'fantasy']),
('Book 4', ARRAY['adventure']);
然后，創(chuàng)建 GIN 索引：
CREATE INDEX idx_books_tags_gin ON books USING GIN (tags);
現(xiàn)在，我們可以執(zhí)行搜索查詢，以便在 tags 列中查找包含特定標簽的書籍。例如，查找包含標簽 'fiction' 的書籍：
SELECT * FROM books WHERE tags @> ARRAY['fiction'];
lxm=# set enable_seqscan = off;
SET
lxm=# explain (verbose, analyse, costs, buffers) SELECT * FROM books WHERE tags @> ARRAY['fiction'];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.books (cost=8.00..12.01 rows=1 width=254) (actual time=0.023..0.025 rows=2 loops=1)
Output: id, title, tags
Recheck Cond: (books.tags @> '{fiction}'::text[])
Heap Blocks: exact=1
Buffers: shared hit=3
-> Bitmap Index Scan on idx_books_tags_gin (cost=0.00..8.00 rows=1 width=0) (actual time=0.017..0.018 rows=2 loops=1)
Index Cond: (books.tags @> '{fiction}'::text[])
Buffers: shared hit=2
Planning:
Buffers: shared hit=1
Planning Time: 0.126 ms
Execution Time: 0.073 ms
(12 rows)
lxm=#
lxm=# SELECT * FROM books WHERE tags @> ARRAY['fiction'];
id | title | tags
----+--------+---------------------
1 | Book 1 | {fiction,adventure}
2 | Book 2 | {science,fiction}
(2 rows)
這將返回匹配的書籍記錄。
下面 SQL ,將返回同時包含 'fiction' 和 'adventure' 標簽的書籍記錄。
SELECT * FROM books WHERE tags @> ARRAY['fiction', 'adventure'];
lxm=# SELECT * FROM books WHERE tags @> ARRAY['fiction', 'adventure'];
id | title | tags
----+--------+---------------------
1 | Book 1 | {fiction,adventure}
(1 row)
lxm=# explain (verbose, analyse, costs, buffers) SELECT * FROM books WHERE tags @> ARRAY['fiction', 'adventure'];
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.books (cost=12.00..16.01 rows=1 width=254) (actual time=0.016..0.017 rows=1 loops=1)
Output: id, title, tags
Recheck Cond: (books.tags @> '{fiction,adventure}'::text[])
Heap Blocks: exact=1
Buffers: shared hit=4
-> Bitmap Index Scan on idx_books_tags_gin (cost=0.00..12.00 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (books.tags @> '{fiction,adventure}'::text[])
Buffers: shared hit=3
Planning:
Buffers: shared hit=1
Planning Time: 0.087 ms
Execution Time: 0.034 ms
(12 rows)
jsonb 類型
當使用 PostgreSQL 的 JSONB 數(shù)據(jù)類型存儲和查詢 JSON 數(shù)據(jù)時，可以使用 GIN（Generalized Inverted Index）索引來提高查詢性能。GIN 索引適用于包含大量不同的鍵值對的 JSONB 列。
下面是一個 PostgreSQL 中使用 GIN 索引的 JSONB 示例：
首先，創(chuàng)建一個包含 JSONB 列的表：
drop table if exists my_table ;
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO my_table (data)
VALUES ('{"name": "John", "age": 30, "address": {"city": "New York", "state": "NY"}}'),
('{"name": "Alice", "age": 25, "address": {"city": "San Francisco", "state": "CA"}}'),
('{"name": "Bob", "age": 35, "address": {"city": "Seattle", "state": "WA"}}');
接下來，創(chuàng)建一個 GIN 索引來加速 JSONB 列的查詢：
CREATE INDEX my_table_data_gin_index ON my_table USING GIN (data);
現(xiàn)在，可以使用 GIN 索引來執(zhí)行 JSONB 列的查詢。例如，查找居住在紐約的人：
set enable_seqscan to off;
SELECT * FROM my_table WHERE data @> '{"address": {"city": "New York"}}';
explain (verbose, analyse, costs, buffers) SELECT * FROM my_table WHERE data @> '{"address": {"city": "New York"}}';
lxm=# set enable_seqscan to off;
SET
lxm=#
lxm=# SELECT * FROM my_table WHERE data @> '{"address": {"city": "New York"}}';
id | data
----+-----------------------------------------------------------------------------
1 | {"age": 30, "name": "John", "address": {"city": "New York", "state": "NY"}}
(1 row)
lxm=# explain (verbose, analyse, costs, buffers)
lxm-# SELECT * FROM my_table WHERE data @> '{"address": {"city": "New York"}}';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.my_table (cost=16.00..20.01 rows=1 width=36) (actual time=0.022..0.022 rows=1 loops=1)
Output: id, data
Recheck Cond: (my_table.data @> '{"address": {"city": "New York"}}'::jsonb)
Heap Blocks: exact=1
Buffers: shared hit=5
-> Bitmap Index Scan on my_table_data_gin_index (cost=0.00..16.00 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (my_table.data @> '{"address": {"city": "New York"}}'::jsonb)
Buffers: shared hit=4
Planning:
Buffers: shared hit=1
Planning Time: 0.068 ms
Execution Time: 0.040 ms
(12 rows)
這將返回居住在紐約的人的記錄。
GIN 索引還可以在 JSONB 列的鍵上進行查詢。例如，查找年齡大于等于 30 歲的人：
SELECT * FROM my_table WHERE data ->> 'age' >= '30'; explain (verbose, analyse, costs, buffers) SELECT * FROM my_table WHERE data ->> 'age' >= '30'; create index idx_my_table_data_age on my_table using gin((data->>'age'));
這將返回年齡大于等于 30 歲的人的記錄。
使用 GIN 索引可以加快對 JSONB 列的查詢，尤其是在包含大量不同鍵值對的情況下。請根據(jù)你的具體需求和數(shù)據(jù)模式進行調(diào)整和優(yōu)化。
全文搜索
當在 PostgreSQL 中使用 GIN 索引進行全文搜索時，可以使用 tsvector 和 tsquery 數(shù)據(jù)類型以及相關的函數(shù)來實現(xiàn)。以下是一個示例：
假設我們有一個表 articles，其中有一個列 content 存儲了文章的內(nèi)容。我們想要創(chuàng)建一個 GIN 索引來支持全文搜索功能。
首先，創(chuàng)建 articles 表：
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(100),
content TEXT
);
接下來，插入一些示例數(shù)據(jù)：
INSERT INTO articles (title, content) VALUES
('Article 1', 'This is the content of article 1.'),
('Article 2', 'Here is the content for article 2.'),
('Article 3', 'This article discusses various topics.'),
('Article 4', 'The content of the fourth article is different.');
然后，為 content 列創(chuàng)建一個輔助列 tsvector，該列將存儲已處理的文本索引：
ALTER TABLE articles ADD COLUMN content_vector tsvector;
接下來，更新 content_vector 列的值，將 content 列的文本轉換為 tsvector 類型：
UPDATE articles SET content_vector = to_tsvector('english', content);
現(xiàn)在，我們可以創(chuàng)建 GIN 索引：
CREATE INDEX idx_articles_content_gin ON articles USING GIN (content_vector);
這將創(chuàng)建一個基于 content_vector 列的 GIN 索引，以支持全文搜索。
接下來，我們可以執(zhí)行全文搜索查詢，使用 tsquery 類型來指定搜索條件。例如，查找包含單詞 'content' 的文章：
SELECT * FROM articles WHERE content_vector @@ to_tsquery('english', 'content');到此這篇關于PostgreSQL中GIN索引使用場景的文章就介紹到這了,更多相關PostgreSQL GIN索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
本文主要介紹了PostgreSQL中GIN索引的三種使用場景,包括數(shù)組類型、JSONB類型和全文搜索,具有一定的參考價值,感興趣的可以了解一下2025-07-07
PostgreSQL核心原理之數(shù)據(jù)庫偶爾會卡頓的原因分析
PostgreSQL功能強大、穩(wěn)定可靠的開源關系型數(shù)據(jù)庫系統(tǒng),廣泛應用于各種規(guī)模的企業(yè)和項目中,本文將從PostgreSQL的核心原理出發(fā),深入剖析導致“偶爾卡頓”的常見原因,并結合底層機制進行解釋,幫助 DBA 和開發(fā)者理解問題本質,從而更有效地排查與優(yōu)化,感興趣的朋友一起看看吧2026-02-02
無公網(wǎng)IP環(huán)境下的PostgreSQL遠程訪問方案
本文提出了一種基于內(nèi)內(nèi)網(wǎng)穿透技術的PostPostQL遠程訪問解決方案,該方案無需公網(wǎng)IP,配置簡單且安全性可控,支持擴展性強,通過三步實現(xiàn)：隧道建立、端口映射和身份驗證,實測延遲5-ms、帶寬NMbps,適用于開發(fā)、數(shù)據(jù)查詢和報表導出場景,需要的朋友可以參考下2026-04-04
這篇文章主要介紹了postgresql高級應用之合并單元格,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-05-05
PostgreSQL 向量擴展插件pgvector安裝和使用
本文主要介紹了PostgreSQL 向量擴展插件pgvector安裝和使用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2026-05-05
postgresql使用過程中常用到的一些sql語句，方便我們快速使用查詢一些操作特整理一下，方便需要的朋友2022-11-11
這篇文章主要介紹了postgreSQL 數(shù)字與字符串類型轉換操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案
這篇文章主要介紹了PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgres數(shù)據(jù)庫安裝、配置、使用DBLink的實例詳解
文章介紹PostgreSQL的DBLink技術,用于跨數(shù)據(jù)庫實例查詢,解決數(shù)據(jù)分散問題,支持多種數(shù)據(jù)庫類型,安裝需檢查插件并配置連接參數(shù),便于實現(xiàn)多實例數(shù)據(jù)匯總,感興趣的朋友跟隨小編一起看看吧2025-07-07
本文主要介紹了在Windows上通過Navicat遠程連接PostgreSQL的配置方法,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-06-06