Some time ago someone posted on Reddit that they have problems adding Polish configuration to their PostgreSQL.
While checking it, I found some interesting news. And of course figured out how to have Polish configuration…
Tag: tsearch
Do you really need tsvector column?
When using tsearch one usually, often, creates a tsvector column to put data in, and then create index on it.
But, do you really need the index? I wrote once already that you don't have to, but then a person talked with me on IRC, and pointed this section of docs:
One advantage of the separate-column approach over an expression index … Another advantage is that searches will be faster, since it will not be necessary to redo the to_tsvector calls to verify index matches.
So, let's see how big of a problem it really is.
How to index data with tsearch?
Someone asked recently on Slack, whether one should build tsvector data in a field, and index it, or make index on expression.
We talked about it for a while, and I thought I'll reformat my thoughts into blogpost to avoid retyping it next time.
Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function
On 7th of April 2018, Teodor Sigaev committed patch:
Add json(b)_to_tsvector function Jsonb has a complex nature so there isn't best-for-everything way to convert it to tsvector for full text search. Current to_tsvector(json(b)) suggests to convert only string values, but it's possible to index keys, numerics and even booleans value. To solve that json(b)_to_tsvector has a second required argument contained a list of desired types of json fields. Second argument is a jsonb scalar or array right now with possibility to add new options in a future. Bump catalog version Author: Dmitry Dolgov with some editorization by me Reviewed by: Teodor Sigaev Discussion: https://www.postgresql.org/message-id/CA+q6zcXJQbS1b4kJ_HeAOoOc=unfnOrUEL=KGgE32QKDww7d8g@mail.gmail.com
Continue reading Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function
Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb
On 31st of March 2017, Andrew Dunstan committed patch:
Full Text Search support for json and jsonb The new functions are ts_headline() and to_tsvector. Dmitry Dolgov, edited and documented by me.
Continue reading Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb
Waiting for 9.6 – Phrase full text search.
On 7th of April, Teodor Sigaev committed patch:
Phrase full text search. Patch introduces new text search operator (<-> or <DISTANCE>) into tsquery. On-disk and binary in/out format of tsquery are backward compatible. It has two side effect: - change order for tsquery, so, users, who has a btree index over tsquery, should reindex it - less number of parenthesis in tsquery output, and tsquery becomes more readable Authors: Teodor Sigaev, Oleg Bartunov, Dmitry Ivanov Reviewers: Alexander Korotkov, Artur Zakirov
Waiting for 9.6 – Tsvector editing functions
On 11th of March, Teodor Sigaev committed patch:
Tsvector editing functions Adds several tsvector editting function: convert tsvector to/from text array, set weight for given lexemes, delete lexeme(s), unnest, filter lexemes with given weights Author: Stas Kelvich with some editorization by me Reviewers: Tomas Vondram, Teodor Sigaev
Continue reading Waiting for 9.6 – Tsvector editing functions
Joining BTree and GIN/GiST indexes
Today, I'd like to show you how you can use the same index for two different types of conditions. One that is using normal BTree indexing ( equal, less than, greater than ), and one that is using GIN/GiST index, for full text searching.
Why I’m not fan of TSearch?
Before I'll start let me say that I am fan of what Oleg and Teodor did – their work is great, and I do appreciate their time and ideas.
But – I simply don't like the idea of using FTS (Full Text Search) inside of database. Why? Let me show you.
Waiting for 8.4 – partial-match support in GIN, and sequence restart
Today we have two interesting patches:
- patch by Teodor Sigaev and Oleg Bartunov, and committed by Tom Lane, which adds interesting capability to GIN indexes
- patch by Zoltan Boszormenyi, also committed by Tom, which adds “RESTART" option to ALTER SEQUENCE. With some interesting consequences
Continue reading Waiting for 8.4 – partial-match support in GIN, and sequence restart