Linked Questions

1 vote
1 answer

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 ...
sudo's user avatar
  • 981
1 vote
0 answers

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 ...
jataf1's user avatar
  • 11
25 votes
3 answers

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 ...
Nick's user avatar
  • 353
29 votes
2 answers

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 ...
David Eyk's user avatar
  • 527
31 votes
1 answer

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 ...
Iain Samuel McLean Elder's user avatar
18 votes
1 answer

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 ...
xangr's user avatar
  • 437
18 votes
2 answers

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 ...
Christian Schmitt's user avatar
16 votes
1 answer

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 (...
bl0b's user avatar
  • 343
11 votes
2 answers

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 ...
Denis Stephanov's user avatar
12 votes
1 answer

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 | [email protected] arya | vidal | foo@...
Victor's user avatar
  • 223
8 votes
2 answers

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", "...
Benjewman's user avatar
  • 183
5 votes
3 answers

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
Tommy's user avatar
  • 63
6 votes
1 answer

'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"; ...
Balázs E. Pataki's user avatar
3 votes
3 answers

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 LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
Christian Schmitt's user avatar
12 votes
1 answer

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'), ...
user3098231's user avatar

15 30 50 per page