Linked Questions
49 questions linked to/from Pattern matching with LIKE, SIMILAR TO or regular expressions
1
vote
1
answer
2k
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 ...
- 981
1
vote
0
answers
399
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 ...
- 11
25
votes
3
answers
13k
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 ...
- 353
29
votes
2
answers
33k
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 ...
- 527
31
votes
1
answer
32k
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 ...
- 2,318
18
votes
1
answer
26k
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 ...
- 437
18
votes
2
answers
34k
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 ...
16
votes
1
answer
14k
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 (...
- 343
11
votes
2
answers
19k
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 ...
- 277
12
votes
1
answer
16k
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 | [email protected]
arya | vidal | foo@...
- 223
8
votes
2
answers
9k
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", "...
- 183
5
votes
3
answers
3k
views
Full Text Search With PostgreSQL
i have a table with this rows:
Stickers
------------------------------------------------------
ID | Title |Keywords (ts_vector)
------------------------------------------------------
...
- 63
6
votes
1
answer
20k
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";
...
3
votes
3
answers
6k
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 ...
12
votes
1
answer
7k
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'),
...
- 131