5

I've been experimenting with tsvector indexes for Full Text Search, and see that it's a common practice to generate an store a vector in a column of type tsvector. We're on Postgres 11.4, but I've already seen this very practice used as an example for PG 12 generated columns. (Simpler than using a trigger for the same purpose.)

My question is, what is the benefit? I tried an expression GIN index on the tsvector of a text field, and a GIN index on the stored tsvector. With around 8M rows locally, I couldn't measure any meaningful speed difference. Given that storing the vector as a column and an index takes more space, I'm curious if there are obvious cases where the extra cost is justified. For example, when you've got a lot more roles.

Note: We're storing the text in the database, so this isn't one of those setups where you index an external page/document/etc without absobing the source text into the database.

1
  • I don't think there is any benefit in retaining a redundant derived column, it is just a waste of space. Oct 17, 2019 at 6:06

2 Answers 2

6

If you use proximity searching (like "phraseto_tsquery" for example), with a functional index it will have to re-parse each match candidate document to a tsvector and check it for the right sequence and spacing of words. This can be quite slow, especially if the number of candidates is much higher than the number of final results. If the tsvector is stored, it can just read it and not reparse the document, this is much faster. I think other advanced features like "ts_headline" may be in the same situation--but I haven't tested them.

Even if you just use "@@", I think that if the bitmap of the number of results doesn't fit in "work_mem", then it will also need to reparse the documents in order to recheck candidate matching blocks which "overflowed". Of course in that case increasing "work_mem" would be likely be a better option than adding a column.

For what it is worth, if you use RUM rather than GIN, it will solve the the problem with phraseto_tsquery on functional indexes.

1
  • That's all good food for thought. I have been using only to_tsquery and then the <#> distance operator. I'll check out the other tsquery options. And, yes, I'd definitely consider using a RUM index (they sound great), but we're deployed on RDS. They haven't compiled in RUM support, and RUM isn't in core. So, no RUM for me :( Oct 17, 2019 at 20:40
0

Just anecdotally, I had a GIN index created over a column, with some large values (doing document text extraction, so multiple paragraphs if not many pages of text per row).

Queries were taking 60+s depending on the query, moving to a precomputed vector column + index improved those queries significantly, down to 2-3s on "bad ones".

Basically I just followed the steps of https://www.postgresql.org/docs/14/textsearch-tables.html "12.2.2. Creating Indexes", where you start with a functional index then at the end transition to the "column + index" setup.

So I would highly recommend anyone who is asking this question benchmark their setup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.