All Questions
Tagged with postgresql-performance pattern-matching
10 questions
0
votes
2
answers
93
views
How to search for the acronym of a string column in an indexable way
I'm working on an express.js project, using Sequelize as the orm, and postgres as the engine.
I'm working on improving the performance of a slow select query that does many things, and one of them is ...
- 534
1
vote
1
answer
50
views
Efficient self-join on column value being the prefix of join column
How can I efficiently turn a set of strings into a table such that each strings is mapped to all other strings of which it is a prefix?
I use PostgreSQL 14 and deal with a table medical_codes of about ...
- 94.7k
1
vote
2
answers
935
views
Matching performance with pattern from table column
I have a query which looks like:
SELECT *
FROM my_table
WHERE 'some_string' LIKE mytable.some_column || '%%'
How can I index some_column to improve this query performance?
Or is the a better way to ...
- 2,331
3
votes
1
answer
4k
views
PostgreSQL GIN index slower than GIST for pg_trgm?
Despite what all the documentation says, I'm finding GIN indexes to be significantly slower than GIST indexes for pg_trgm related searches. This is on a table of 25 million rows with a relatively ...
- 43
2
votes
1
answer
892
views
Reverse string with leading wildcard scan in Postgres
SQL Server: Index columns used in like?
I've tried using the query method in the link above with Postgres (0.3ms improvement), it seems to only work with MySQL (10x faster).
MYSQL
User Load (0.4ms) ...
4
votes
2
answers
805
views
Why does a slight change in the search term slow down the query so much?
I have the following query in PostgreSQL (9.5.1):
select e.id, (select count(id) from imgitem ii where ii.tabid = e.id and ii.tab = 'esp') as imgs,
e.ano, e.mes, e.dia, cast(cast(e.ano as varchar(4))...
- 5,169
1
vote
1
answer
258
views
GIN index not used for small table when 0 rows returned
In a Postgres 9.4 database, I created a GIN trigram index on a table called 'persons' that contains 1514 rows like the following:
CREATE INDEX persons_index_name_1 ON persons
USING gin (lower(name) ...
- 815
11
votes
3
answers
16k
views
Query performance with concatenation and LIKE
Can someone explain the performance difference between these 3 queries?
concat() function:
explain analyze
select * from person
where (concat(last_name, ' ', first_name, ' ', middle_name) like '%...
- 292
2
votes
1
answer
3k
views
Look up in a white space separated string in Postgres
I have a character varying field in postgres containing a 1-white-space-separated set of strings. E.g.:
--> one two three <--
--> apples bananas pears <--
I put --> and <-- to show ...
- 7,617
2
votes
1
answer
566
views
Performance impact of empty LIKE in a prepared statement
I have set a GiST pg_trgm index on the name column of the files table.
The simplified query of the prepared statement looks like this:
SELECT * FROM files WHERE name LIKE $1;
The $1 param will be ...
user187676