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"}';
