It seems like all possible substrings of your input are delimited with the character
/. To illustrate my point:
SELECT string_agg(part, '/') OVER (ORDER BY ord) AS prefix
FROM string_to_table('example.org/sub/test.html', '/') WITH ORDINALITY s(part, ord)
ORDER BY ord DESC;
Building on that assumption, here is a highly efficient solution to overcome the principal obstacle Daniel explained:
CREATE OR REPLACE FUNCTION my_longest_prefix(_url text)
RETURNS SETOF domain
LANGUAGE plpgsql ROWS 1 STABLE AS
FOR _prefix IN
SELECT string_agg(part, '/') OVER (ORDER BY ord)
FROM string_to_table(_url, '/') WITH ORDINALITY s(part, ord)
ORDER BY ord DESC
FROM domain d
WHERE d.domain = _prefix;
EXIT WHEN FOUND;
SELECT * FROM my_longest_prefix('example.org/sub/sub/sub/test.html');
The function exits after the first hit. You get no row if nothing is found.
The "magic" is to switch operands in the filter expression so that the input can be the pattern and the query is "sargable" again.
Also, after splitting the input to its atomic parts, we can match with
= instead of
LIKE. Now, left & right of the operator don't matter any more because
= has a
COMMUTATOR (itself) - as opposed to
LIKE which does not. Related:
If the input can really be truncated at any position, replace the start of the loop with:
FOR _prefix IN
SELECT string_agg(part, '') OVER (ORDER BY ord)
FROM string_to_table(_url, null) ...
Only slightly less efficient. Results in one index seek per character in the input. But you'll probably take at least the leading domain as pattern ...
All you need is a plain B-tree index on
(domain). From what you told us, there may already be a
UNIQUE constraint or index on the column providing said index.
string_to_table() requires Postgres 14 or later. Earlier versions offer various alternatives. The solution does not hinge on this implementation detail.