0

Lets say I have a table like so:

webpages

id  |  url
------------------------
 1  |  http://example.com/path
 2  |  example2.biz/another/path
 3  |  https://www.example3.net/another/path

And I want to search which webpages' url column is a substring of an input string. I know I can do it like this:

SELECT id FROM webpages WHERE STRPOS(url, 'example.com/path/to/some/content') > 0;

Expected result: 1

But I'm not sure how I might optimize this kind of query to run faster. Is there a way?

I found this article, it seems to suggest not - though I'm not sure if that's still true as it's from over a decade ago.

https://www.postgresql.org/message-id/046801c96b06%242cb14280%248613c780%24%40r%40sbcglobal.net

osdiab
  • 1,972
  • 3
  • 26
  • 37
  • 2
    Surely it is possible. For example: https://www.postgresql.org/docs/current/pgtrgm.html In particular: https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.40.7 – Abelisto Aug 28 '19 at 00:07
  • That says it works with LIKE and ILIKE but says nothing about STRPOS. Is there documentation somewhere saying it will help with this query? Or do you know a way to use LIKE queries to search for matches where the column value is a substring of an input value (not the other way around as LIKE queries are typically used)? – osdiab Aug 28 '19 at 07:26
  • You can try something like `SELECT id FROM webpages WHERE url % 'example.com/path/to/some/content' /* index could be used here */ and STRPOS(url, 'example.com/path/to/some/content') > 0;` Just play with `pg_trgm.similarity_threshold` option. – Abelisto Aug 28 '19 at 10:27

0 Answers0