Index for searching in column where the column starts with the provided value
2
votes
3
answers
1595
views
I have a table, where one column contains a substring of an url. It's always the leftmost part of the url without scheme.
| id | domain | column1 | column2 |
|---|--------|---------|---------|
| 1 | example.com | value1 | value2 |
| 2 | example.org | value3 | value4 |
| 3 | example.net/en-US | value5 | value6
| 4 | example.net/en-GB | value7 | value8
There's never an overlap in the domains, so there won't be a row with domain
example.net
, nor a domain that's empty.
I want to search and find the matching row given an url, for example example.org/sub/sub/sub/test.html
it should return the row with id 2.
So far I've used SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%')
which gives what I want, but it always does a seq scan of the table, even though I have an index on the domain
column. I was hoping that Postgres could make some conclusions and use the index anyway.
How can I improve the performance of this?
Asked by Zyberzero
(123 rep)
Mar 17, 2023, 04:58 PM
Last activity: Jun 9, 2025, 11:39 PM
Last activity: Jun 9, 2025, 11:39 PM