When creating a database in Postgres over a field with phone numbers (there are no pluses and brackets in the field, but the data type was decided to use the Test (Text)) of the BTREE index. But during the use, it was decided to create indexing for full-text search. Btree copes normally using the LIKE search operator here in this case: ‘792886273%’, but in the case when we first use the symbol%, the search works for a very long time (‘% 8627315’). Question: What type of index will advise to use in this case? Thank you

Answer 1

So for full-text search or for Like '% foo '? What are different things.

btree, of course, is not suitable for like '% Foo' . It is impossible to use the wood effectively if we do not know where to turn on the first fork.

like '% foo' or like '% foo%' can be accelerated using a trigmam search implemented by the extension PG_TRGM with GIN or GIST index

create extension pg_trgm;
CREATE INDEX TRGM_IDX ON TEST_TRGM USING GIST (T GIST_TRGM_OPS);
CREATE INDEX TRGM_IDX ON TEST_TRGM USING GIN (T GIN_TRGM_OPS);