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
1
vote
0 answers

Django TrigramSimilarity returns errors with Full Text Search on GIN index

Hello I'm trying to make search on Django with postgresql/FTS on a GIN indexed column but get a weird error. This error does ot appear on CharField but only on a SearchVectorField: the Postgresql Database has the pg_trgm extension installed (within…
David
  • 492
  • 8
  • 17
1
vote
2 answers

Matching a small table (<1,000 rows) to a large table (>100m rows) using pg_trgm—most efficient method?

This is a problem that comes up often in my work with various different data sets, so please excuse me presenting it in general terms rather than using a specific example. I often need to get records from a large table (generally 100s of millions of…
Louis Goddard
  • 45
  • 1
  • 5
1
vote
2 answers

Turn off recheck in trgm index

We have a postgres column that uses the trigrams index (pg_trgm). The index works well but is VERY slow: the final recheck for false positives consumes >99% of the overall query time (bitmap index scan 0.25s; recheck 30.7s). In our case, false…
user124114
  • 8,372
  • 11
  • 41
  • 63
1
vote
1 answer

Trigram Index ORDER BY optimization

I am trying to implement a search function and after some investigation (see this interesting read by Yorick Peterse at GitLab) I decided I would opt for the trigram approach using the pg_trgm extension. I'd like to return the 10 most relevant…
Kathandrax
  • 914
  • 14
  • 26
1
vote
1 answer

Django trigram_similar

I've been trying to implement trigram_similar functionality in my Django backend code. I get an error: web_1 | Traceback (most recent call last): web_1 | File…
nwar1994
  • 53
  • 5
1
vote
0 answers

pg_trgm, similarity and finding duplicate questions by their answers

I have a table named Questions. It has the content field (which contains a question) and an answer field. The whole table has around 22k rows and I'm trying to figure out how to search for duplicate questions based on common answers (although I'm…
ere
  • 1,739
  • 3
  • 19
  • 41
1
vote
1 answer

Rails 5, Postgres, ActiveRecord - how to include function value as model field

I am using the Trigram module (pg_trgm) for Postgres in a Rails 5 application for text search. I have been able to successfully find satisfactory results using the following call to find a limit number of records whose names are similar to the…
Michael Morgan
  • 11
  • 2
1
vote
1 answer

Is there a way to use pg_trgm like operator with btree indexes on PostgreSQL?

I have two tables: table_1 with ~1 million lines, with columns id_t1: integer, c1_t1: varchar, etc. table_2 with ~50 million lines, with columns id_t2: integer, ref_id_t1: integer, c1_t2: varchar, etc. ref_id_t1 is filled with id_t1 values ,…
Nicolas
  • 23
  • 1
  • 7
1
vote
1 answer

Change GUC parameter before subquery PostgreSQL

I'm currently doing a query like such: SELECT * FROM ( (SELECT * from A WHERE first_name % 'fakeFirstName') UNION (SELECT * from B WHERE last_name % 'fakeLastName') ) AS result; Both A and B are views of the same underlying table C,…
Olivier Nappert
  • 85
  • 1
  • 6
1
vote
2 answers

How to group by similar values with pg_trgm

I have the following table id error - ---------------------------------------- 1 Error 1234eee5, can not write to disk 2 Error 83457qwe, can not write to disk 3 Error 72344ee, can not write to disk 4 Fatal barier breach on object 72fgsff 5 …
Roman
  • 567
  • 3
  • 17
0
votes
0 answers

Postgres similarity (or text search) matching partial document

Call me an amateur full-text search dev, here... I've read some tutorials, but now hit a bit of a wall. The following uses Postgres's pg_trgm module: => select similarity('Foo', 'Foo Bar'); similarity ------------ 0.5 If 'Foo Bar' were a…
Sander Verhagen
  • 8,540
  • 4
  • 41
  • 63
0
votes
2 answers

How to achieve sub 1s Trigram/Vector search in Postgres when searching long strings

I'm compiling a database of decently sized documents (anywhere from 20 to 500 characters), and I would like to be able to very quickly (under 1s) search through them. I started with around 100k rows worth of data, and now have around 4 million. I…
oseun22
  • 3
  • 2
0
votes
1 answer

postgres join table with condition and limitation

I need to join 6 tables by item_name, (The DB of each table is lower than 10k items except 1 table with 30k,table_1 is the samllest with 5k items) I join tables by same id or name where them both are b-tree indeces, the join by name based on the…
Bennyh961
  • 85
  • 1
  • 7
0
votes
0 answers

Postgres not using trgm index with Concat

I created a trigram index on full name where full_name = first_name || ' ' || last_name. CREATE INDEX "user_full_name_trgm" ON "user" using gin (upper(first_name || ' ' || last_name) gin_trgm_ops); I am using django queryset to filter over…
Shashwat Kumar
  • 5,159
  • 2
  • 30
  • 66
0
votes
0 answers

How could I optimize this SQL request to find similarity in name ? (pg_trgm.similarity)

I have created a query which takes about 15 min to complete with 20.000+ records What I'm trying to do is pairing clients with similarity in their full name My current query : SELECT a.full_name, a.id, b.id FROM debtors as a INNER JOIN …
Miklw
  • 173
  • 1
  • 1
  • 6
1 2
3
4