undefined | Better HN

0 pointsngrilly9y ago0 comments

> That’s actually quite unproblematic, if you have the tsvector as its own column (not just as index).

Yes, it works, but it is slow because the tsvector is usually big enough to be stored in a TOAST table, and this produces a lot of random access reads.

This is why there is a project of storing additional information in the GIN (term positions) in order for the index to contain all necessary information for the ranking:

https://wiki.postgresql.org/images/2/25/Full-text_search_in_...

0 comments

kuschku9y ago

> usually big enough to be stored in a TOAST table

Ah, luckily, in my case, that can’t happen – each row’s message contains one IRC message, so at most 512 bytes. That also automatically ensures we’ll never run into TOAST issues.

ngrillyOP9y ago

Now I understand why you didn't suffer from the slow ranking issue. In my test case, text is longer and triggers the TOAST management code.

kuschku9y ago

Yeah, if your vectors are in TOAST, you really have a huge issue with ranking. There’s no simple way to get around that, except with customized solutions like Lucene/Solr/ES

j / k navigate · click thread line to collapse

0 pointsngrilly9y ago0 comments

> That’s actually quite unproblematic, if you have the tsvector as its own column (not just as index).

Yes, it works, but it is slow because the tsvector is usually big enough to be stored in a TOAST table, and this produces a lot of random access reads.

This is why there is a project of storing additional information in the GIN (term positions) in order for the index to contain all necessary information for the ranking:

https://wiki.postgresql.org/images/2/25/Full-text_search_in_...

0 comments

kuschku9y ago

> usually big enough to be stored in a TOAST table

Ah, luckily, in my case, that can’t happen – each row’s message contains one IRC message, so at most 512 bytes. That also automatically ensures we’ll never run into TOAST issues.

ngrillyOP9y ago

Now I understand why you didn't suffer from the slow ranking issue. In my test case, text is longer and triggers the TOAST management code.

kuschku9y ago

Yeah, if your vectors are in TOAST, you really have a huge issue with ranking. There’s no simple way to get around that, except with customized solutions like Lucene/Solr/ES

j / k navigate · click thread line to collapse