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

Giriş

GIN Index kullanmak için iki tane yönterm var.

1. Verimli Yöntem

Örnek

Şöyle yaparız

-- Table with just raw data
CREATE TABLE documents (
  id serial PRIMARY KEY,
  title text,
  body text
);

-- Efficient GIN index
CREATE INDEX idx_docs_search ON documents 
USING gin(to_tsvector('english', title || ' ' || body));

-- Query uses the same expression
SELECT * FROM documents 
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgres & gin');

2. Verimsiz Yöntem

Bu yöntemin kötü tarafı şöyle

Most of the time you don’t want to store the data three times (raw in table, vector in table, lexeme in index) so people use the to_search_vector / tokenization call in the GIN index as an expression

Yani tsvector sütun tipi fazlalık.

2.1 Genel Kullanım

Tabloda tsvector tipinden bir sütun olur. Bu sütun için GIN indeks yaratırız.

CREATE INDEX indexname ON mytable USING GIN(tsvector_column_name);

1. tsvector tipindeki sütunu doldurmak için INSERT işleminden sonra bir tane de UPDATE gerekiyor

2. Bu sütunu sorgulamak için plainto_tsquery() veya to_tsquery() kullanılır

- plainto_tsquery() daha basit sorgulamalar içindir

- to_tsquery() daha karmaşık sorgulamalar içindir. AND, OR, NOT, * (wildcard), ? (wildcard), : (prefix), ~ (fuzzy match) gibi şeyleri destekler

Örnek

Şöyle yaparız

SELECT * FROM mytable WHERE tsvector_column_name @@ plainto_tsquery('my first story');

Örnek

Şöyle yaparız

ALTER TABLE storm_events ADD COLUMN episode_narrative_tsv tsvector;
    
UPDATE storm_events SET episode_narrative_tsv = to_tsvector(episode_narrative);

CREATE INDEX tsv_idx ON storm_events USING GIN(episode_narrative_tsv);

Örnek

Şöyle yaparız

-- Create the table
CREATE TABLE blog_posts (
 id SERIAL PRIMARY KEY,
 content TEXT
);

-- Insert sample data
INSERT INTO blog_posts (content)
VALUES
 ('Welcome to my blog on alternative of Elasticsearch'),
 ('In this blog post, we explore the power of ts_vector and GIN indexing'),
 ('PostgreSQL offers robust full-text search capabilities'),
 ('Searching for keywords in PostgreSQL has never been easier');

- Create `ts_vector` column and GIN index
ALTER TABLE blog_posts ADD COLUMN document tsvector;
UPDATE blog_posts SET document = to_tsvector('english', content);
CREATE INDEX gin_index ON blog_posts USING GIN(document);

-- Perform full-text search
SELECT * FROM blog_posts
  WHERE document @@ to_tsquery('english', 'power & search');

Örnek

Elimizde şöyle tablolar olsun. Burada users ve users'a ait stories isimli iki tablo var. stories içinde tsvector tipinden ismi fulltext olan bir sütun var

--Create Users table
CREATE TABLE IF NOT EXISTS users
(
  id BIGSERIAL NOT NULL,
  name CHARACTER VARYING(100) NOT NULL,
  rating INTEGER,
 PRIMARY KEY (id)
);

CREATE INDEX usr_rating_idx ON users USING BTREE (rating ASC NULLS LAST)
TABLESPACE pg_default;

--Create Stories table
CREATE TABLE  IF NOT EXISTS stories
(
  id BIGSERIAL NOT NULL,
  create_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  num_views BIGINT NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  fulltext TSVECTOR,
  user_id BIGINT,
  PRIMARY KEY (id),
  CONSTRAINT user_id_fk FOREIGN KEY (user_id)
  REFERENCES users (id) MATCH SIMPLE
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
  NOT VALID
);

CREATE INDEX str_bt_idx ON stories USING BTREE
  (create_date ASC NULLS LAST, num_views ASC NULLS LAST, user_id ASC NULLS LAST);

CREATE INDEX fulltext_search_idx ON stories USING GIN (fulltext);

stories tablosuna satır eklemek şöyle. 

INSERT INTO stories (create_date, num_views, title, body, user_id)
                                            VALUES (?, ?, ?, ?, ?)

Daha sonra TSVECTOR tipindeki sütunu güncelleriz.

UPDATE stories SET fulltext = to_tsvector(title || ' ' || body) where id = ?

Sorgulamak için şöyle yaparız. plainto_tsquery() metodu ile string'i aramak istenile tipe çeviririz.

SELECT
 s.id id,
 create_date,
 num_views,
 title,
 body,
 user_id,
 name user_name,
 rating user_rating
FROM stories s INNER JOIN users u
 ON s.user_id = u.id
WHERE true
 AND rating BETWEEN 0 AND 100
 AND num_views BETWEEN 0 AND 100   
 AND create_date BETWEEN '2023-08-28' AND '2023-08-29'   
 AND fulltext @@ plainto_tsquery('Second story')  
ORDER BY create_date DESC OFFSET 0 LIMIT 100
;