Linked Questions

0
votes
1answer
236 views

Find partial match with textsearch query

I have the following query that I did hard looking on this site: SELECT * FROM renns WHERE ( to_tsvector('english', coalesce(numar_admin, '')) || to_tsvector('english', coalesce(...
1
vote
1answer
940 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
0answers
206 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 ...
2
votes
1answer
100 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....
0
votes
1answer
513 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 ...
4
votes
4answers
1k views

How to remove an element from a col with a CSV of ints in PostgreSQL 9.2?

I have PostgreSQL 9.2 database and a table: id integer, allowed_types character varying(255)` Sample data as below: id allowed_types 1 3,4,5,13,14 How I can remove 4 and 5 from allowed_types ...
5
votes
2answers
130 views

Optimize query with SIMILAR TO and many prefixes

I have a query in PostgreSQL 9.6 that is taking a particularly long time to run: SELECT DISTINCT ON (gc.number) gc.number, gc.code, ch.client_id, ch.client_parent_id FROM client gc, ...
6
votes
1answer
11k views

Equivalent of UTF8_UNICODE_CI collation in PostgreSQL

I would like a column in a table inside a PostgreSQL database (I am using version 9.6). I know of the UTF8_UNICODE_CI collation on MySQL, so I tried: CREATE TABLE thing ( id BIGINT PRIMARY KEY ...
3
votes
2answers
3k views

Effectively searching through entire 1 level nested JSONB [closed]

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
3
votes
1answer
393 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE UNACCENT(LOWER(a.name)...
14
votes
2answers
25k views

PostgreSQL LIKE query on ARRAY field

Is there any way to have a Postgres LIKE query on a ARRAY field? Currently I want something like that: SELECT * FROM list WHERE lower(array_field) LIKE '1234%' Currently lower is not needed that ...
12
votes
1answer
13k views

Faster query with pattern-matching on multiple text fields

I have one Postgres table with more than 20M tuples: first_name | last_name | email ------------------------------------------- bat | man | batman@wayne.com arya | vidal | foo@...
7
votes
1answer
5k views

Can jsonb do fast search by *value* or even a partial value? Other types?

I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice. Example data: id: 1, name: "Joe Doe", preferences: { color: "red" , ...
3
votes
1answer
4k views

Index on column with data type citext not used

In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), md5(...
11
votes
1answer
9k views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE (...

15 30 50 per page