Postgres 14

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 assets (
  id TEXT,
  title TEXT,
  ts tsvector 
   GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A')) STORED
);


CREATE TABLE


INSERT INTO assets (id, title) VALUES ('a', 'Hello world!'),
  ('b', 'Hello sir'),
  ('c', 'I am above the world'),
  ('d', 'World hello')

INSERT 0 4


SELECT
    asset.id,
    (
        select
            jsonb_agg(results ORDER BY results.rank DESC)
        FROM (
            SELECT
                searchresult.id,
                searchresult.title,
                resultsForWord.rank
            FROM
                assets searchresult
            CROSS JOIN LATERAL 
            (
                SELECT ts_rank(ts, to_tsquery ('english', word)) rank
                FROM UNNEST(
                    string_to_array(TRIM(regexp_replace(asset.title, '[^a-zA-Z+]', ' ', 'g')), ' ')
                ) as word
                WHERE ts_rank(ts, to_tsquery ('english', word)) > 0.5
                ORDER BY rank DESC
                LIMIT 1
            ) AS resultsForWord
            WHERE
                searchresult.id != asset.id
            ORDER BY rank DESC
            LIMIT 5
        ) results
     ) results
FROM
    assets asset
WHERE asset.id = 'a';


id
results


a
[{"id": "b", "rank": 0.6079271, "title": "Hello sir"}, {"id": "c", "rank": 0.6079271, "title": "I am above the world"}, {"id": "d", "rank": 0.6079271, "title": "World hello"}]



SELECT 1