PostgreSQL: Create Index on Full Text Search tsvector Data

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing an example of applying Full Text Search on PostgreSQL Table with Index.

Here, I am using tsvector for full text search which is document type and uses match operator like @@.

PostgreSQL: Example of Trigram Index for Full Text Search using pg_trgm Extension

Below is an example:

Create a sample table with data:

CREATE TABLE tbl_Textdata

(id int, txt character varying);

INSERT INTO tbl_Textdata VALUES

(1,'I am DBA'),(2,'database research & development'),(3,'think for data only')

,(4,'data is important'),(5,'data is a future'),(6,'I am data owner');

Do Full Text Searching:

SELECT txt

FROM tbl_Textdata

WHERE to_tsvector(txt) @@ to_tsquery('data');

Result:

txt

--------------------------

'think for data only'

'data is important'

'data is a future'

'I am data owner'

Apply an Index on require Text Pattern:

1	CREATE INDEX tbl_Textdata_txt_idx ON tbl_Textdata USING GIN (to_tsvector('english',txt));

Check the execution plan:

explain analyze

SELECT txt

FROM tbl_Textdata

WHERE to_tsvector(txt) @@ to_tsquery('data');

'Seq Scan on tbl_textdata (cost=0.00..4.07 rows=1 width=32) (actual time=0.043..0.058 rows=4 loops=1)'

' Filter: (to_tsvector((txt)::text) @@ to_tsquery('data'::text))'

' Rows Removed by Filter: 2'

'Planning time: 5.494 ms'

'Execution time: 0.083 ms'

Jul 22, 2019Anvesh Patel