Generalized Inverted Index - GIN Index - JSONB Sütun Tipine Göre Index Full Text Search İçindir

Giriş

JSONB Sütun Tipi için 2 çeşit GIN index yaratılabilir. Açıklaması şöyle

A set of GIN-specific operators that can be used in a query depends on the operator class specified at index creation. For JSONB, supported classes are:

jsonb_ops — the default operator class, which provides two categories of operators: checking the existence of keys and checking the existence of values based on JSONPath expressions or key-value pairs.

jsonb_path_ops — provides only the latter category and offers better performance for these operators.

jsonb_path_ops Tipi

Açıklaması şöyle

Create a GIN index on the JSONB column using the jsonb_path_ops operator class. This limits the operator to only @>, but that operator allows equality comparisons on any number of properties located at different levels within the JSONB document in a single WHERE criteria.

Örnek

Şöyle yaparız

CREATE TABLE sample_jsonb (id serial NOT NULL PRIMARY KEY, data jsonb);

INSERT INTO sample_jsonb (data) VALUES 
('{"name":"First", "count":12, "orderDate":"2022-03-03T12:14:01", "extra":"some text"}');

INSERT INTO sample_jsonb (data) VALUES
('{"name": "Second", "count": 23, "orderDate": "2022-04-03T15:17:01"}');

CREATE INDEX sample_jsonb_path_ops_idx
ON sample_jsonb USING GIN (data jsonb_path_ops);

-- select all rows where the name property is equal to "First" a
SELECT * FROM sample_jsonb WHERE data @> '{"name":"First"}'::jsonb 

-- select all rows where count is greater than 15
SELECT * FROM sample_jsonb WHERE data @@ '$.count > 15'

Örnek

@> operator yani containment operator kullanıyorsak gerekir. Bir index oluşturalım

CREATE INDEX gin_data ON books_data using gin(data); 

Bir sorgu çalıştıralım.  Gin indeksinin kullanıldığını görebiliriz.

EXPLAIN ANALYZE SELECT * FROM books_data WHERE data @> '{"author" : "Mark Figueroa"}';