Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE domain (
  id integer GENERATED ALWAYS AS IDENTITY
, domain text
, col1 int
, col2 int
);

INSERT INTO domain (domain, col1, col2) VALUES
  ('example.com',       '1', '2')
, ('example.org',       '3', '4')
, ('example.net/en-US', '5', '6')
, ('example.net/en-GB', '7', '8')
;

CREATE TABLE

INSERT 0 4

-- illustration
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;

prefix

example.org/sub/test.html

example.org/sub

example.org

SELECT 3

CREATE OR REPLACE FUNCTION my_longest_prefix(_url text)
  RETURNS SETOF domain
  LANGUAGE plpgsql ROWS 1 STABLE AS
$func$
DECLARE
   _prefix text;
BEGIN
   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
   LOOP
      RETURN QUERY
      SELECT d.*
      FROM   domain d
      WHERE  d.domain = _prefix;
      
      EXIT WHEN FOUND;
   END LOOP;
END
$func$;

CREATE FUNCTION

SELECT * FROM my_longest_prefix('example.org/sub/sub/sub/test.html');
SELECT * FROM my_longest_prefix('example.net/en-US');
SELECT * FROM my_longest_prefix('example.net/en-US/bla/blubb/some.html');
SELECT * FROM my_longest_prefix('example.com');
SELECT * FROM my_longest_prefix('not.there.yet');

id
domain
col1
col2

2
example.org
3
4

SELECT 1

id
domain
col1
col2

3
example.net/en-US
5
6

SELECT 1

id
domain
col1
col2

3
example.net/en-US
5
6

SELECT 1

id
domain
col1
col2

1
example.com
1
2

SELECT 1

id
domain
col1
col2

SELECT 0