PostgreSQL中GIN索引的三種使用場景
數(shù)組類型
當在 PostgreSQL 中使用 GIN 索引來處理數(shù)組類型時，可以通過以下示例來說明：
假設(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ù)類型以及相關(guān)的函數(shù)來實現(xiàn)。以下是一個示例：
假設(shè)我們有一個表 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 列的文本轉(zhuǎn)換為 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');到此這篇關(guān)于PostgreSQL中GIN索引使用場景的文章就介紹到這了,更多相關(guān)PostgreSQL GIN索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
postgresql流復制原理以及流復制和邏輯復制的區(qū)別說明
這篇文章主要介紹了postgresql流復制原理以及流復制和邏輯復制的區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgresql數(shù)據(jù)合并,多條數(shù)據(jù)合并成1條的操作
這篇文章主要介紹了postgresql數(shù)據(jù)合并,多條數(shù)據(jù)合并成1條的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了postgresql中的時間戳格式化問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
解決sqoop import 導入到hive后數(shù)據(jù)量變多的問題
這篇文章主要介紹了解決sqoop import 導入到hive后數(shù)據(jù)量變多的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
今天小編就為大家分享一篇關(guān)于Ubuntu PostgreSQL安裝和配置的介紹，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
Win11安裝PostgreSQL數(shù)據(jù)庫的兩種方式詳細步驟
PostgreSQL是備受業(yè)界青睞的關(guān)系型數(shù)據(jù)庫,尤其是在地理空間和移動領(lǐng)域,這篇文章主要介紹了Win11安裝PostgreSQL數(shù)據(jù)庫的兩種方式詳細步驟,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-04-04
這篇文章主要介紹了淺談pg_hint_plan定制執(zhí)行計劃操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL 實現(xiàn)sql放入文件批量執(zhí)行
這篇文章主要介紹了PostgreSQL 實現(xiàn)sql放入文件批量執(zhí)行，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
Linux CentOS 7源碼編譯安裝PostgreSQL9.5
這篇文章主要為大家詳細介紹了Linux CentOS 7源碼編譯安裝PostgreSQL9.5的相關(guān)資料,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-11-11
在PostgreSQL中設(shè)置表中某列值自增或循環(huán)方式
這篇文章主要介紹了在PostgreSQL中設(shè)置表中某列值自增或循環(huán)方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01