Linked Questions

0
votes
1answer
810 views

Postgres won't use btree index in left-anchored LIKE query [duplicate]

Using Postgres 9.5. The Postgres documentation says that the btree index can be used for left-anchored "like" queries (i.e. "%" only at end). But my left-anchored "like" queries on a 1 billion row ...
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 ...
23
votes
3answers
9k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
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 ...
20
votes
1answer
17k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
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 ...
8
votes
2answers
18k 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 ...
9
votes
1answer
8k 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 (...
9
votes
1answer
9k 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@...
5
votes
3answers
2k views

Full Text Search With PostgreSQL

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

PostgreSQL full text search on many columns

I need an advice with searching of record based on specified string. Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the ...
2
votes
3answers
3k views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
5
votes
2answers
4k views

Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns?

For example, say I have a table with a medium number of rows (~100,000 or so) that has a jsonb column with the following example data in one of the rows: {"name":"Bob", "...
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'), ...
4
votes
1answer
8k views

DBLINK match a value from a local table

Am trying to run the following dblink start transaction; select dblink_connect('host=remote port=5432 dbname=remote_db user=user password=pass'); insert into local_table (column1, column2 ) select * ...
4
votes
1answer
8k views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
5
votes
1answer
8k 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
2k views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no ...
2
votes
2answers
1k views

What is more efficient: text (765) or varchar (765) to store a freeform searchable description?

EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back? A little background on the question. Let's say we have sales_orders table and we want to be able to keep a ...
6
votes
1answer
4k 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" , ...
5
votes
2answers
968 views

How to make this query use my multicolumn index?

Currently, I have a view which is defined like this: View "public.customer_list" Column | Type | Modifiers | Storage | Description -----------+---------...
3
votes
3answers
699 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 ...
3
votes
1answer
3k 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(...
7
votes
1answer
1k views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
3
votes
1answer
3k views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
2
votes
2answers
1k 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 ...
4
votes
1answer
1k views

PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key

If I have a table containing: CREATE TABLE test( id SERIAL PRIMARY KEY, name VARCHAR(200), age INT, data JSONB ); and data column populated with {"name": xxx, "age": yyy}, sometimes {"name": ...
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: ...
1
vote
1answer
2k views

Postgres pg_trgm JOIN multiple columns with large tables (~50 million rows)

I'm trying to perform a query on Postgres using the pg_trgm extension that attempts to fuzzy match across multiple columns in two tables. I've let the query run for an hour or more and it hasn't ...
0
votes
1answer
2k views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...

15 30 50 per page