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

Giriş

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
;