Linked Questions

2
votes
1answer
71 views

Trigram similarity (pg_trgm) with German umlauts

I try to figure out how to improve Postgres 10.6 pg_trgm queries with German umlauts (äöü). In german 'ö' can be written as 'oe'. But beware: not every 'oe' can be written as 'ö'. CREATE TABLE public....
13
votes
1answer
13k views

Get partial match from GIN indexed TSVECTOR column

I would like to get results by query this: SELECT * FROM ( SELECT id, subject FROM mailboxes WHERE tsv @@ plainto_tsquery('avail') ) AS t1 ORDER by id DESC; This works and return rows with tsv ...
1
vote
1answer
38 views

Postgres HAVING ilike any wildcard in array_agg

I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is: SELECT p.id, p.name, array_agg(vo.name) FROM product p LEFT ...
1
vote
1answer
78 views

improve speed of fuzzy search

I have configured trigram index for fuzzy search in Postgres. I inserted 8500000 rows to this table with 4 words text. CREATE DATABASE people; \connect people; CREATE TABLE person ( id ...
1
vote
0answers
126 views

Query 4.5 million record postgresql database quickly [duplicate]

I have a SQL Server database with 4.5 million records. I reproduced the same database with the exact same 4.5 million records in a PostgreSQL database. When querying the SQL Server database, I am ...
24
votes
2answers
16k views

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
3
votes
3answers
703 views

Postgres trigram match acting strange for specific characters

I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query: SELECT my_column, similarity('$ Hello', my_column) AS sml ...
2
votes
1answer
93 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
9
votes
1answer
3k views

How to search hyphenated words in PostgreSQL full text search?

I have to search for hyphenated words like 'good-morning', 'good-evening', etc. My query is: select id, ts_headline(content, to_tsquery('english','good-morning'), ...
5
votes
3answers
2k views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
2
votes
2answers
1k views

Trigram index for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
0
votes
2answers
91 views

PostgreSQL how to speed up ORDER BY with text column

Table schema: CREATE SEQUENCE fsa_online_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; CREATE TABLE "public"."fsa_online" ( "id" integer DEFAULT nextval('...
2
votes
1answer
1k views

Optimize a trigram search with custom sort order

I'm new to typeahead / full text searching but I'm trying to figure out an algorithm that yields the best results for my search use-case. The additional module pg_trgm is installed. I have a table ...
-1
votes
1answer
165 views

Indexing the condition lower(f) LIKE '%foo%'

How do I index the following statement with an un-anchored search pattern SELECT somefield FROM sometable WHERE lower(somefield2) like '%foo%'; Some rows have more than 2k bytes.
0
votes
1answer
353 views

Queries using GIN index on f_unaccent() seem slow?

I have almost 20M rows in one table that contains product, including their names. I wish to search fulltext and fast by the name so I created this index: CREATE INDEX ...

15 30 50 per page