Full text search, PostgreSQL

Language: Layout:

CREATE TABLE document (
doc_id varchar PRIMARY KEY,
title varchar,
content varchar
);

CREATE TABLE doc_spec_sets (
tc_set_id varchar PRIMARY KEY,
doc_id varchar,
spec varchar
);

insert into document values('doc1', 'quickly for the visualiser that', 'with the structure I mentioned');
insert into document values('doc2', 'The main arguments', 'if your data was static');
insert into document values('doc3', 'We’ve had issues', 'columns so we need');
insert into document values('doc4', 'assessed our options', 'Replace both occurences');
insert into document values('doc5', 'So even though', 'full text of documents');
insert into document values('doc6', 'records and we found', 'documents, it added');
insert into document values('doc7', 'the speed deteriorates', 'rather than a slower query');
insert into document values('doc8', 'but we found', 'network overhead rather');
insert into document values('doc9', 'Then you need to fill options', 'then it only added');

insert into doc_spec_sets values('tc1', 'doc1', 'documents');
insert into doc_spec_sets values('tc2', 'doc2', 'occurences');
insert into doc_spec_sets values('tc3', 'doc3', 'rather');
insert into doc_spec_sets values('tc4', 'doc4', 'options');
insert into doc_spec_sets values('tc5', 'doc5', 'documents');
insert into doc_spec_sets values('tc6', 'doc6', 'mentioned');
insert into doc_spec_sets values('tc7', 'doc3', 'options');
insert into doc_spec_sets values('tc8', 'doc4', 'structure');
insert into doc_spec_sets values('tc9', 'doc1', 'network');

--------------------------------------------------------------
-- create function

CREATE OR REPLACE FUNCTION make_tsvector(title varchar, content varchar)
   RETURNS tsvector AS $$
BEGIN
  RETURN (setweight(to_tsvector('english', title),'A') ||
    setweight(to_tsvector('english', content), 'B'));
END
$$ LANGUAGE 'plpgsql' IMMUTABLE;

-- create index

CREATE INDEX IF NOT EXISTS idx_fts_articles ON document
  USING gin(make_tsvector(title, content));

-- select 
-- work

SELECT 
  doc_id, 
  title,
  content
FROM document 
WHERE
  make_tsvector(title, content) @@ to_tsquery('documents & added');

select
    document.doc_id,
    document.title,
    document.content,
    doc_spec_sets.spec
from doc_spec_sets
  LEFT OUTER join document on doc_spec_sets.doc_id = document.doc_id
where 
  doc_spec_sets.spec = 'documents'
  or 
  doc_spec_sets.spec = 'added'
group by document.doc_id, doc_spec_sets.spec;


absolute service time: 0,46 sec