65 questions
0
votes
1
answer
54
views
PostgreSQL GIN index size stuck at 0 bytes
I have the following kind of a database running in Azure Cosmos DB for PostgreSQL (latest version of PostgreSQL ans citus):
testingtable
id SERIAL NOT NULL PRIMARY KEY,
first_name TEXT NOT NULL,...
- 118
1
vote
1
answer
2k
views
operator class "gin_trgm_ops" does not exist for access method "gin"
psycopg2.errors.UndefinedObject: operator class "gin_trgm_ops" does not exist for access method "gin"
Hello everybody, this is the whole message when I try to run pytest on my ...
1
vote
1
answer
109
views
Postgres: How to optimize similarity queries searching columns on multiple tables? (pg_trgm)
I am trying to optimize a similarity (word_similarity) query, which searches multiple columns on 2 tables.
The tables and their indices look like this:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- ...
- 1,902
1
vote
0
answers
606
views
How to create a Postgres GIST Index (gist_trgm_ops) with two concatenated fields using Prisma ORM
Let's assume I have table with multiple columns. For instance:
id int
first_name varchar(50)
first_name varchar(50)
example record would be
+----+------------+------------+
| id | first_name | ...
- 21
1
vote
1
answer
138
views
Jooq and postgres: pg_trgm operators in plain sql produce error "operator does not exist"
Technologies I am using: java, spring boot, jooq, postgres with pg_trgm extension, r2dbc.
I am trying to use pg_trgm operators for a simple search on Postgres, but jooq throws an error.
Code sample:
...
- 13
0
votes
2
answers
441
views
Postgres ignores my gin index for a similarity query
In my table (around 12 million rows), I have a text field called full_name and the id.
I also have the following indexes:
create index entities_full_name_gin_trgm_ops_index
on temp.entities
using ...
- 1,993
1
vote
1
answer
367
views
Postgres ilike combined with similarity pg_trgm?
I'm experimenting with pg_trgm in my codebase for search. So far it works really well for search queries > 4 characters, which makes sense given how pg_trgm works.
There are however several ...
- 5,104
1
vote
1
answer
109
views
PostgreSQL pg_trgm, longer search terms slower issue
I'm experiencing an issue with pg_trgm, where the longer the query length, the longer the execution time.
Is this normal?
Below is a configuration and test way
Configure temp tables and indexes
...
- 11
0
votes
0
answers
565
views
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');
...
- 9,048
0
votes
2
answers
2k
views
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 ...
- 3
9
votes
2
answers
5k
views
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 ...
- 375
0
votes
1
answer
403
views
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 ...
- 169
0
votes
0
answers
144
views
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 || ' ' || ...
- 5,287
2
votes
2
answers
2k
views
How to index a column for leading wildcard search and check progress?
My table has 650M rows (according to a fast but decently precise estimate from a query I found here).
It has a text column called receiver_account_id, and I need to be able to search those records ...
- 23.8k
0
votes
0
answers
48
views
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, ...
- 333
0
votes
1
answer
192
views
Real number comparison for trigram similarity
I am implementing trigram similarity for word matching in column comum1. similarity() returns real. I have converted 0.01 to real and rounded to 2 decimal digits. Though there are rank values greater ...
- 19
1
vote
1
answer
191
views
Implementing K-Gram indexing in postgres
I am trying to implement an index in postgres for wild card queries such as
SELECT * FROM TABLENAME WHERE COL1 LIKE '<text>%';
I know postgres offers gin and gist trigram indexing through ...
- 158
1
vote
1
answer
1k
views
How to search many values with Postgres trigram?
If I have a table with the values name and surname, how do I search for those two values?
Example
I set a threshold to filter out unwanted values, and then I can find similarity with table name and ...
- 375
0
votes
1
answer
141
views
Error while running fuzzy search using pg_trgm extension
I have created pg_trgm extension in postgres. Application works fine for querying for several days. But after few days, gives an error ERROR: operator does not exist: character varying % text
Hint: No ...
- 3
0
votes
1
answer
173
views
How do I fetch similar posts from database where text length can be more than 2000 characters
As far as I'm aware, there is no simple, quick solution. I am trying to do a full-text keyword or semantic search, which is a very advanced topic. There are dedicated search servers created ...
- 43
-1
votes
1
answer
117
views
Can this postgresql query be further optimized?
we're building a translation editor and one of the main use cases is to find similar translations in the database. The main entities are: segment, translation_record and user. Segment can be either ...
- 396
0
votes
0
answers
162
views
PostgreSQL search using like does not return right value
I am using pg_trgm in my name column as :
create index if not exists idx_gin_iperson_name on iperson using gin (name gin_trgm_ops)
in iperson table, I have a column with name Jose Goruir and in ...
- 2,924
4
votes
1
answer
8k
views
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 ...
- 963
1
vote
1
answer
3k
views
Postgres function raising error when calling from Python script but running fine in PgAdmin
I have a postgres function called 'medrealize()' inside which i am creating temp table and creating index for temp tables using below lines
CREATE INDEX SQLOPS_RefICD_ICD10_idx ON t$...
- 833
0
votes
1
answer
664
views
Set GUC parameter or use PGOPTIONS environment variable with PgBouncer
Can I make PgBouncer preserve the PGOPTIONS environment variable in transaction pooling to configure GUC parameters? Or is there another way to configure these parameters in PgBouncer so that it ...
- 871
6
votes
1
answer
661
views
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 ...
- 73
0
votes
1
answer
1k
views
Install pg_trgm on postgres 9.4.24
I'm trying to use the similarity function on a Greemplum system using postgres 9.4.24 version.
The Greenplum System is running on a CentOS 7 cluster (CentOS Linux release 7.9.2009 (Core))
I've managed ...
- 589
3
votes
2
answers
1k
views
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 ...
- 69
1
vote
0
answers
274
views
how to use pg_trgm operators e.g (<-> ) in python
I'm using the pg_trgm for similarity search on PostgreSQL DB and I need to return the results to the PostGIS table, but I'm getting this programmer error, I learned that this error is related to ...
2
votes
1
answer
307
views
Strange sorting behavior with bigint column via GiST index in PostgreSQL
I'm working on implementing a fast text search in PostgreSQL 12.6 (Ubuntu 20.04.2 VBox) with custom sorting, and I'm using pg_trgm along with GiST (btree_gist) index for sorted output. The idea is to ...
- 23
1
vote
0
answers
490
views
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 ...
- 502
2
votes
1
answer
2k
views
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 ...
- 15.7k
5
votes
1
answer
3k
views
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 ...
- 683
1
vote
2
answers
243
views
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 ...
1
vote
2
answers
678
views
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 ...
- 8,652
2
votes
2
answers
3k
views
Will trigram index on multiple columns make search faster and how to make such search properly?
Let's assume I have table with multiple columns. For instance:
id int
name text
surname text
cars json
example record would be
+----+------+---------+------------------------------------+
| id | ...
- 403
12
votes
1
answer
4k
views
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
+-------+------------...
- 4,707
8
votes
2
answers
364
views
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....
- 4,517
4
votes
1
answer
4k
views
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 ...
- 8,652
2
votes
0
answers
261
views
Django Postgres search with TrigramSimilarity
If I use 'title' in my post_search function it works perfectly.
Post.objects.annotate(
similarity=TrigramSimilarity('title', query)
).filter(...
2
votes
0
answers
271
views
Find fuzzy duplicates on single column rows with pg_trgm
I was trying to find duplicates on column rows, but as they are fuzzy (not the same value, misspelling, indent space) I have to use pg_trgm extensions and similarity() function to find those. The ...
- 430
2
votes
2
answers
1k
views
How to use pg_trgm operators(e.g. %>) in django framework?
I'm using the pg_trgm for similarity search on PostgreSQL DB and I need to return the results to the front by using the Django model. But, I got a problem that the operator %> cannot be recognized ...
- 23
1
vote
1
answer
1k
views
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 ...
- 1,034
2
votes
1
answer
1k
views
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 "/usr/local/lib/...
- 61
1
vote
0
answers
114
views
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 ...
- 1,769
3
votes
0
answers
980
views
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 ...
- 4,707
2
votes
0
answers
361
views
Postgres 9.5 cache lookup failed
I am trying to install pg_trgm into postgres (Using postgres 9.5 on ubuntu 16) by doing CREATE EXTENSION pg_trgm. The first executable line of pg_trgm--1.1.sql is
CREATE FUNCTION set_limit(float4)
...
- 306
0
votes
1
answer
222
views
Fuzzy date search/Optimizing LIKE with date type
Well, I will be pretty straightforward - I encountered with huge performance drop when implementing birthday search in my app - I'm using LIKE and this is not left-anchored expression (as date column ...
- 430
1
vote
1
answer
305
views
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 ...
3
votes
1
answer
2k
views
postgresql pg_trgm.word_similarity_threshold decreasing
SET pg_trgm.word_similarity_threshold TO 0.2;
lowers the threshold for current session
but does not do it for database. I need to lower the threshold for supporting spelling mistakes.
- 31