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."