Stack Overflow
Stack Overflow

Questions tagged [pg-trgm]

Postgres trigram match module

Postgres trigram match module https://www.postgresql.org/docs/current/static/pgtrgm.html

58 questions
10
votes
1 answer

Optimizing a postgres similarity query (pg_trgm + gin index)

I have defined the following index: CREATE INDEX users_search_idx ON auth_user USING gin( username gin_trgm_ops, first_name gin_trgm_ops, last_name gin_trgm_ops ); I am performing the following query: PREPARE…
Anentropic
  • 32,188
  • 12
  • 99
  • 147
9
votes
1 answer

Fulltext search combined with fuzzysearch in PostgreSQL

I want to realize a fulltext search in postgresql combined with a kind of fuzzy search. For my testarea I followed up this article: https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/ and everything is working fine. But…
Chris
  • 121
  • 1
  • 9
8
votes
1 answer

Edge NGram search in PostgreSQL

I need to make search-as-you-type autocomplete for a large list of companies (over 80,000,000). The company name should contain the word that starts with a search query like this +-------+----------------------------------+ | term | results …
Yevhen Bondar
  • 4,357
  • 1
  • 11
  • 31
8
votes
2 answers

How to make a fast pg_trgm DESC (descending)?

I have a list of 100.000 sentences in a table, with pg_trgm I can get the closest ones of my string "super cool" very fast with a GIN/GIST index. See the official example : https://www.postgresql.org/docs/11/pgtrgm.html Sadly, I want the opposite,…
Laurent Debricon
  • 4,307
  • 2
  • 24
  • 26
6
votes
2 answers

How to improve or speed up Postgres query with pg_trgm?

Are there any additional steps I can take to speed up query execution? I have a table with more than 100m rows and I need to do search for matching strings. For that I checked two options: Compare text with to_tsvector @@ (to_tsquery or…
Dmiich
  • 325
  • 2
  • 16
6
votes
1 answer

Search in 300 million addresses with pg_trgm

I have 300 million addresses in my PostgreSQL 9.3 DB and I want to use pg_trgm to fuzzy search the rows. The final purpose is to implement a search function just like Google Map search. When I used pg_trgm to search these addresses, it cost about…
Gary Tao
  • 61
  • 1
  • 6
4
votes
0 answers

AWS Postgres setting pg_trgm.word_similarity_threshold

I'm trying to set the pg_trgm.word_similarity_threshold GUC parameter on an RDS postgres (13) instance. I have tried setting it with a post-deployment SQL script: SET pg_trgm.word_similarity_threshold = 0.5; SELECT pg_reload_conf(); But this…
PolarGiraffe
  • 53
  • 4
4
votes
1 answer

Postgres `gin_trgm_ops` index not being used

I'm trying to speed up some text matching in Postgres, using the pg_trgm extensions: CREATE TABLE test3 (id bigint, key text, value text); insert into test3 values (1, 'first 1', 'second 3'); insert into test3 values (2, 'first 1', 'second…
user124114
  • 8,372
  • 11
  • 41
  • 63
4
votes
1 answer

How is the similarity calculated in Postgres pg_trgm module

Can somebody explain to me exactly how the similarity function is calculated in Postgres pg_trgm module. e.g. similarity('sage', 'message') = 0.3 1) " s"," sa",age,"ge ",sag 2) " m"," me",age,ess,"ge ",mes,sag,ssa n1: cardinality(1) = 5 n2:…
swami
  • 673
  • 1
  • 9
  • 18
4
votes
1 answer

How to create pg_trgm compound indexes with date columns

SELECT col1, max(date) as max_date FROM table WHERE col1 ILIKE 'name' GROUP BY col1 Here col1 is varchar and date is timestamp with time zone data type. So created extension CREATE EXTENSION pg_trgm Then tried the following indexes and got the…
Atihska
  • 4,803
  • 10
  • 56
  • 98
3
votes
1 answer

How to make PostgreSQL use gin_trgm_ops index for equality comparison

There is a table and a gin index, Insert 1,000,000 random numbers. 0 < number < 100,000. Test two equivalent queries create table Test ( id serial primary key, code varchar(255) not null ); create index Test_code_gin on Test using gin…
user10339780
  • 953
  • 7
  • 13
3
votes
2 answers

Index jsonb column keys using GIN and pg_trgm, for ILIKE queries in Rails

I have a table "Leads" with the following structure : # == Schema Information # # Table name: leads # # id :integer not null, primary key # data :jsonb not null # state …
the_namikaze
  • 69
  • 6
3
votes
0 answers

PostgreSQL GIN index for BIGINT column

I have table tbl with columns - data TEXT - fk_id BIGINT - text TEXT There is over 100M records, and ~1K different values for fk_id. I need to run query like this SELECT * FROM tbl WHERE fk_id=12345 AND text LIKE '%abcdef% I tried to use extension…
Yevhen Bondar
  • 4,357
  • 1
  • 11
  • 31
2
votes
1 answer

Better Postgres trigram ranking

I'm searching several million names and addresses in a Postgres table. I'd like to use pg_trgm to do fast fuzzy search. My app is actually very similar to the one in Optimizing a postgres similarity query (pg_trgm + gin index), and the answer there…
ccleve
  • 15,239
  • 27
  • 91
  • 157
2
votes
1 answer

How to install Django pg_trgm by makemigrations?

I have a Django app and a Postgresql database (in production). Now I want to intall pg_trgm extension for Postgres. But I can't find any step-by-step instructions for installing it from Django app. I have a superuser status. How to do it correctly?
Vit Amin
  • 574
  • 5
  • 20
1
2 3 4