GIN index for full-text search in PostgreSQL

Introduction

A GIN index helps find words quickly in large text data. It makes searching faster when you look for specific words or phrases.

You want to search for keywords inside articles or documents stored in your database.

You have a large collection of product descriptions and want users to find products by words.

You need to support fast search in user comments or reviews.

You want to filter emails or messages by text content quickly.

You want to improve performance of queries that use full-text search functions.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING GIN (to_tsvector('language', column_name));

Replace 'language' with the text language like 'english' for better word processing.

The to_tsvector function converts text into searchable tokens for the index.

Examples

This creates a GIN index on the 'content' column of the 'articles' table for English text.

PostgreSQL

CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));

This index speeds up full-text search on the 'review_text' column in the 'reviews' table.

PostgreSQL

CREATE INDEX idx_reviews_text ON reviews USING GIN (to_tsvector('english', review_text));

Sample Program

This example creates a table of books with descriptions, adds a GIN index on the description column, and searches for books related to 'cooking'.

PostgreSQL

CREATE TABLE books (id SERIAL PRIMARY KEY, title TEXT, description TEXT);

INSERT INTO books (title, description) VALUES
('Learn SQL', 'A book about learning SQL basics'),
('Cooking Tips', 'Tips and tricks for cooking delicious meals'),
('Gardening Guide', 'How to grow plants and flowers');

CREATE INDEX idx_books_description ON books USING GIN (to_tsvector('english', description));

SELECT id, title FROM books WHERE to_tsvector('english', description) @@ to_tsquery('cooking');

Important Notes

GIN indexes are best for columns with many distinct words.

Creating a GIN index can take time on large tables but speeds up searches a lot.

Remember to use the same language in to_tsvector and to_tsquery for accurate results.

Summary

GIN indexes make full-text search fast by indexing words inside text columns.

Use to_tsvector to prepare text for indexing and to_tsquery to search.

They are very useful when searching large text data like articles or reviews.