Sample Header Ad - 728x90

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