Postgresql: tsvector full text search

Question

I have a table containing 100m rows and i need to full text search it and provide information about how similar (e.g. with the pg_trgm module) the text's are. Off cause the problem here is that it should be fast.

I tried gist and gin indexes, had a extra column with the tsvector of my field etc.

My idea is to query first using tsvector and after that running the similarity function provided by the pg_trgm module.

My problem is the following. If i use a whole word as my query it will work. But not if i append something.

This makes total sense because the tsvector of "A quick brown fox..." is "'a':1 'brown':3 'fox':4 'quick':2".

I hope i made clear what i would like to achieve.

Example:

works

select to_tsvector('A quick brown fox...') @@ to_tsquery('quick') -- true

does not work

select to_tsvector('A quick brown fox...') @@ to_tsquery('quicks') -- false

Any ideas on how to achieve that using postgresql?

What version of Postgres is that? Second query gives me true on 9.6.2 EDIT: Oh, checked on 9.6.5 and indeed it works as you described. — Łukasz Kamiński
– Łukasz Kamiński, Commented Oct 12, 2017 at 11:18
@ŁukaszKamiński that depends on the dictionary. I get true by default with the "english" dictionary, false if I explicitly use the "simple" dictionary in to_tsquery. This is due to stemming, I think, which would remove the s, but won't just remove any arbitrary characters at the end. — Mad Scientist
– Mad Scientist, Commented Oct 12, 2017 at 11:20
@MadScientist indeed. I tried with to_tsquery('english', 'quicks') and get true. — dknaack
– dknaack, Commented Oct 12, 2017 at 11:23

André Carvalho · Accepted Answer · 2021-09-22 14:26:35Z

1

You need to set the language configuration parameter, like this:

select to_tsvector('english', 'A quick brown fox...') @@ to_tsquery('english', 'quicks')

The capability to recognize lexemes correctly, with plurals and stuff only happens when tsquery and tsvector have the same language configuration.

answered Sep 22, 2021 at 14:26

André Carvalho

2011 gold badge3 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: tsvector full text search

Example:

works

does not work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example:

works

does not work

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related