PostgreSQL中GIN索引的三種使用場(chǎng)景

更新時(shí)間：2025年07月21日 09:39:43 作者：岳麓丹楓001

本文主要介紹了PostgreSQL中GIN索引的三種使用場(chǎng)景,包括數(shù)組類(lèi)型、JSONB類(lèi)型和全文搜索,具有一定的參考價(jià)值,感興趣的可以了解一下

數(shù)組類(lèi)型

當(dāng)在 PostgreSQL 中使用 GIN 索引來(lái)處理數(shù)組類(lèi)型時(shí)，可以通過(guò)以下示例來(lái)說(shuō)明：

假設(shè)有一個(gè)表 books，其中有一個(gè)列 tags 存儲(chǔ)了書(shū)籍的標(biāo)簽信息，使用數(shù)組類(lèi)型來(lái)表示?，F(xiàn)在我們想要?jiǎng)?chuàng)建一個(gè) GIN 索引來(lái)加快對(duì)標(biāo)簽進(jìn)行搜索的查詢(xún)。

首先，創(chuàng)建 books 表：

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    tags TEXT[]
);

接下來(lái)，插入一些示例數(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í)行搜索查詢(xún)，以便在 tags 列中查找包含特定標(biāo)簽的書(shū)籍。例如，查找包含標(biāo)簽 'fiction' 的書(shū)籍：

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)

這將返回匹配的書(shū)籍記錄。

下面 SQL ,將返回同時(shí)包含 'fiction' 和 'adventure' 標(biāo)簽的書(shū)籍記錄。

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 類(lèi)型

當(dāng)使用 PostgreSQL 的 JSONB 數(shù)據(jù)類(lèi)型存儲(chǔ)和查詢(xún) JSON 數(shù)據(jù)時(shí)，可以使用 GIN（Generalized Inverted Index）索引來(lái)提高查詢(xún)性能。GIN 索引適用于包含大量不同的鍵值對(duì)的 JSONB 列。

下面是一個(gè) PostgreSQL 中使用 GIN 索引的 JSONB 示例：

首先，創(chuàng)建一個(gè)包含 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"}}');

接下來(lái)，創(chuàng)建一個(gè) GIN 索引來(lái)加速 JSONB 列的查詢(xún)：

CREATE INDEX my_table_data_gin_index ON my_table USING GIN (data);

現(xiàn)在，可以使用 GIN 索引來(lái)執(zhí)行 JSONB 列的查詢(xún)。例如，查找居住在紐約的人：

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 列的鍵上進(jìn)行查詢(xún)。例如，查找年齡大于等于 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 索引可以加快對(duì) JSONB 列的查詢(xún)，尤其是在包含大量不同鍵值對(duì)的情況下。請(qǐng)根據(jù)你的具體需求和數(shù)據(jù)模式進(jìn)行調(diào)整和優(yōu)化。

全文搜索

當(dāng)在 PostgreSQL 中使用 GIN 索引進(jìn)行全文搜索時(shí)，可以使用 tsvector 和 tsquery 數(shù)據(jù)類(lèi)型以及相關(guān)的函數(shù)來(lái)實(shí)現(xiàn)。以下是一個(gè)示例：

假設(shè)我們有一個(gè)表 articles，其中有一個(gè)列 content 存儲(chǔ)了文章的內(nèi)容。我們想要?jiǎng)?chuàng)建一個(gè) GIN 索引來(lái)支持全文搜索功能。

首先，創(chuàng)建 articles 表：

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    content TEXT
);

接下來(lái)，插入一些示例數(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)建一個(gè)輔助列 tsvector，該列將存儲(chǔ)已處理的文本索引：

ALTER TABLE articles ADD COLUMN content_vector tsvector;

接下來(lái)，更新 content_vector 列的值，將 content 列的文本轉(zhuǎn)換為 tsvector 類(lèi)型：

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)建一個(gè)基于 content_vector 列的 GIN 索引，以支持全文搜索。

接下來(lái)，我們可以執(zhí)行全文搜索查詢(xún)，使用 tsquery 類(lèi)型來(lái)指定搜索條件。例如，查找包含單詞 'content' 的文章：

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

到此這篇關(guān)于PostgreSQL中GIN索引使用場(chǎng)景的文章就介紹到這了,更多相關(guān)PostgreSQL GIN索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

PostgreSQL中GIN索引的三種使用場(chǎng)景

目錄

數(shù)組類(lèi)型

jsonb 類(lèi)型

全文搜索

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線(xiàn)小工具