PostgreSQL – Full Text Search – Day 1

Background

Let us play with Full Text Search against our PostgreSQL table.

Table

model.bible.kjv

Query

Traditional Query

Find Verses :- Jesus and Peter

SQL

select 

          bookID

        , chapterID

        , verseID  

        , verse

from   bible.kjv

where  verse ilike '%Jesus%'

and    verse ilike '%Peter%'

order by
          bookID
        , chapterid
        , verseid

limit 1000 

Output

jesus.peter.01.20190903.1155PM

Full Text Query

Find Verses :- Jesus and Peter

Outline

  1. to_tsvector([column-name])
    • to_tsvector(verse)
  2. to_tsquery
    • Syntax :- to_tsquery([operand])
    • Sample :- to_tsquery(‘(Jesus & Peter)’))

SQL


select
          bookID

        , chapterID

        , verseID  

        , verse

from  bible.kjv tbl

where to_tsvector(verse)

        @@ to_tsquery
        (
            '(Jesus & Peter)'
        )

Output

fulltext.jesus.peter.01.20190904.1200AM.PNG

Query Plan

fulltext.queryPlan.jesus.peter.01.20190904.1210AM

Indexes

Functional indexes can be created to speed things up a bit.

SQL

SQL – Sample


create index if not exists indx_gin_bible_kjv_verse
on bible.kjv
using gin
(
    to_tsvector
    (
          'english'
        , verse
    )
)
;

Full Text Query – Validate Index Usage

Find Verses :- Jesus and Peter

Outline

  1. to_tsvector([language], [column-name])
    • to_tsvector(‘english’, verse)
    • Language ( english) needed because index created on specific language
  2. to_tsquery
    • Syntax :- to_tsquery([operand])
    • Sample :- to_tsquery(‘(Jesus & Peter)’))

SQL


select
          bookID

        , chapterID

        , verseID  

        , verse

from  bible.kjv tbl

where to_tsvector('english', verse)

        @@ to_tsquery
        (
            '(Jesus & Peter)'
        )

Output

fulltext.jesus.peter.01.20190904.1200AM.PNG

Query Plan

fulltext.queryPlan.index.jesus.peter.01.20190904.1218AM

Explain

We can clearly see that our gin index is being used.

 

References

  1. PostgreSQL
    • Full Text Search
      Link
    • Controlling Text Search
      Link
    • Text Search Indexes
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s