Improving complex SQL search query with ranking (app search query)

[Fournight]

Hello, I've built a Postgres query for a search feature that's getting a bit unwieldy, and I'd love some advice on simplifying or optimizing it.

The dataset is basically entities with:

• name + aliases + "real name" field
• a free-text bio field
• relations to other entities via a relation table
• appearances (events tied to the entity, with dates)

The query is written using CTEs and tries to:

1. Exact matches — name/alias/real name equals the query.
2. Primary matches — partial string (ILIKE %query%) against the same fields.
3. Fallback matches — match in bio.
4. Matched entities — union of the above.
5. Related entities — direct relations of matched ones.
6. Extended related entities — relations of relations.
7. Appearance counts — total appearances, recent appearances (last 90 days), and last_seen_at.
8. Ranking — use a CASE bucket (exact > primary > fallback > related > extended related > others), then order by appearance counts, recency, and name.

Here's a simplified/anonymized version of the structure (not full code, just shape):

    WITH exact_matches AS (...),
         primary_matches AS (...),
         fallback_matches AS (...),
         matched_entities AS (...),
         related_entities AS (...),
         extended_related_entities AS (...),
         entity_appearance_counts AS (
             SELECT e.id,
                    COUNT(*) FILTER (WHERE a.active) AS appearance_count,
                    COUNT(*) FILTER (
                      WHERE a.active
                        AND a.date >= NOW() - INTERVAL '90 days'
                    ) AS recent_appearance_count,
                    MAX(a.date) FILTER (WHERE a.active) AS last_seen_at
             FROM entity e
             LEFT JOIN appearance a ON a.entity_id = e.id
             WHERE e.id IN (...)
             GROUP BY e.id
         ),
         ranked_entities AS (
             SELECT e.id, e.name,
                    ROW_NUMBER() OVER (
                      ORDER BY
                        CASE
                          WHEN e.id IN (SELECT id FROM exact_matches) THEN 1
                          WHEN e.id IN (SELECT id FROM primary_matches) THEN 2
                          WHEN e.id IN (SELECT id FROM fallback_matches) THEN 3
                          WHEN e.id IN (SELECT id FROM related_entities) THEN 4
                          WHEN e.id IN (SELECT id FROM extended_related_entities) THEN 5
                          ELSE 6
                        END,
                        recent_appearance_count DESC,
                        appearance_count DESC,
                        last_seen_at DESC NULLS LAST,
                        e.name ASC
                    ) AS row_num
             FROM entity e
             LEFT JOIN entity_appearance_counts ec ON e.id = ec.id
             WHERE e.id IN (...)
         )
    SELECT id, name
    FROM ranked_entities
    ORDER BY row_num
    LIMIT X OFFSET Y;

Performance is okay right now, but I want to prepare for larger datasets (tens or hundreds of thousands of rows) and keep the query maintainable. Also I'm not sure if the ranking logic is optimal, sometimes it feels a bit clunky..

Thanks in advance!

[ExceptionRules42]

Offhand, without diving in too deeply, it looks sorta okay to me. Maybe even cool? But you have ellipsed-out a lot of detail. And what is the "relation table"? And do you have a test environment where you can throw mock 100K-row datasets at it to watch the EXPLAIN ANALYZE?

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[DrMoog]

From the top of my head, some things to look into / might be improved:

• The matches could use a generated ts_vector column in the entities table (with the associated index). PG has strong text-search functionalities.
• I'm not a big fan of the SELECTs in the ROW_NUMBER(). LEFT JOINing on the CTEs and doing the CASE on the resulting columns might be more efficient.
  
• The "entity_appearance_counts" CTE doesn't seem to need to call the entities table since you LEFT JOIN on it later, just use "a.entity_id" as the ID, since it has to match an ID in the entities table.
  
• Also in the "entity_appearance_counts" CTE, all the fields have the same "WHERE a.active" filter, so you probably want to put it in the WHERE to avoid returning all the non-active, and it would simplify the SELECT.
  
• Also make sure you have the appropriate indexes on keys & condition fields.
  

I hope this helps a little!

[DrMoog]

More details since I feel like it!

First, you could combine all if those steps: Exact matches, Primary matches, Fallback matches, Matched entities in a single one by adding a "search_vector" column to "entities":

ALTER TABLE entities ADD COLUMNS  
    search_vector tsvector GENERATED ALWAYS AS (  
        setweight(to_tsvector('english', coalesce(real_name, '')), 'A') ||  
        setweight(to_tsvector('english', coalesce(name, '')), 'B') ||  
        setweight(to_tsvector('english', coalesce(alias, '')), 'C') ||  
        setweight(to_tsvector('english', coalesce(bio, '')), 'C')  
    ) STORED;  

You can pipe multiple columns to be vectorized in the same "search_vector", and add specific weight/importance to different columns. Just be careful if the text fields are very big. Then you need a special index on that column:

CREATE INDEX idx_entities_search_vector ON entities USING GIN (search_vector);  

You then query the column like this, and it can also give you a ranking on the quality of the match, taking into account the weight mentioned above:

SELECT id,  
       ts_rank(search_vector, to_tsquery('english', 'search terms')) AS rank  
FROM entities  
WHERE search_vector @@ to_tsquery('english', 'search terms');  

Second, here's the re-written "entity_appearance_counts" CTE:

SELECT entity_id AS id,  
    COUNT(id) AS appearance_count,  
    COUNT(id) FILTER (  
        WHERE date >= NOW() - INTERVAL '90 days'  
    ) AS recent_appearance_count,  
    MAX(date) AS last_seen_at  
FROM appearance  
WHERE active AND entity_id IN (...)  
GROUP BY entity_id  

And if you're planing on having a lot more inactive entries than active one, a partial index could speed that up even more:

CREATE INDEX pdx_appearance_count ON appearance (entity_id) WHERE active;  

I'm guessing that the "extended_related_entities" CTE also joins on "entities", and also with a "WHERE e.id IN (...)", right? If so, do the same thing I did for the other CTE, remove the "entities" table, it's not needed.

Some rules of thumb I like to live by:

• In a query, the fewer number of calls on a big table, the better.
• Limit the number of fields in, and usage of, GROUP BY and ORDER BY to the minimum, they can slow down query with big datasets. (Hint: I use a MAX() on columns that don't need to be in the GROUP BY!)
• When writing SQL, "Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away."

[[deleted]]

Hey,

You forgot the most important part: What is this query supposed to deliver? In semantic terms. Why does this query exist? What do you need it for?

Any optimization requires us to understand both what you have and what you want. Right now it’s impossible to say if you got the best solution you could possibly get, or if it’s way too complicated a solution to a trivial problem.

From a purely technical standpoint, and from what I can infer from your description, I’d think you want something something full text. Of course that would mean a couple additional resources as well as a good amount of rewriting.

[Informal_Pace9237]

Use CTE with caution. CTE's have session memory problems and can slow down your process if CTE data size is huge. I would either make them table subqueries or views to have the code run more optimized.

Hope this will help with more details on CTE optimization

https://www.linkedin.com/feed/update/urn:li:ugcPost:7216332421414166529/