Why does ORDER BY significantly slow down my query with a computed score of trigram similarity and null field?

Question

I'm working on optimizing a query in PostgreSQL, and I've encountered a performance issue when using the ORDER BY clause. The query is intended to search profiles based on a similarity match to a name (for example: 'john') and then order the results by a computed score. The score is a combination of word similarity and whether the profile has an avatar.

Here's the query:

SELECT
    uuid,
    type,
    byline,
    display_name,
    username,
    avatar,
    (
        word_similarity('john', search_text) +
        CASE WHEN avatar != '' THEN 1 ELSE 0 END
    ) AS combined_score
FROM test_mv_all_profiles
WHERE
    'john' <% search_text
ORDER BY
    combined_score DESC
LIMIT 100;

Explain output:

explain  (Analyze, Buffers)
Limit  (cost=35130.07..35130.32 rows=100 width=52) (actual time=8092.504..8092.575 rows=100 loops=1)
  Buffers: shared hit=66811
  ->  Sort  (cost=35130.07..35158.41 rows=11335 width=52) (actual time=8092.502..8092.565 rows=100 loops=1)
        Sort Key: ((word_similarity('john'::text, search_text) + (CASE WHEN ((avatar)::text <> ''::text) THEN 1 ELSE 0 END)::double precision)) DESC
        Sort Method: top-N heapsort  Memory: 51kB
        Buffers: shared hit=66811
        ->  Bitmap Heap Scan on test_mv_all_profiles  (cost=187.84..34696.86 rows=11335 width=52) (actual time=69.060..8052.737 rows=90765 loops=1)
              Recheck Cond: ('john'::text <% search_text)
              Rows Removed by Index Recheck: 2196849
              Heap Blocks: exact=39716 lossy=26633
              Buffers: shared hit=66811
              ->  Bitmap Index Scan on test_idx_mv_social_profile_search_text_trigram_idx_gin  (cost=0.00..185.01 rows=11335 width=0) (actual time=58.323..58.323 rows=91483 loops=1)
                    Index Cond: ('john'::text <% search_text)
                    Buffers: shared hit=462
Planning time: 0.121 ms
Execution time: 8092.658 ms

The issue: When I remove the ORDER BY combined_score DESC clause, the query runs much faster.

SELECT
    uuid,
    type,
    byline,
    display_name,
    username,
    avatar,
    (
        word_similarity('john', search_text) +
        CASE WHEN avatar != '' then 1 ELSE 0 END
    ) AS combined_score

FROM test_mv_all_profiles

WHERE
    'john' <% search_text

limit 100

Explain output:

Limit  (cost=0.42..392.51 rows=100 width=52) (actual time=0.508..4.430 rows=100 loops=1)
  Buffers: shared hit=311
  ->  Index Scan using test_idx_mv_social_profile_search_text_trigram_idx on test_mv_all_profiles  (cost=0.42..44444.13 rows=11335 width=52) (actual time=0.506..4.417 rows=100 loops=1)
        Index Cond: ('john'::text <% search_text)
        Rows Removed by Index Recheck: 1
        Buffers: shared hit=311
Planning time: 0.118 ms
Execution time: 4.482 ms

My questions:

• Why does the ORDER BY clause slow down the query so much?
• Is there a way to optimize this query while keeping the ORDER BY clause? Would adding an index on the computed score help, and if so, how should I approach that?

Additional Information:

The table test_mv_all_profiles is a materialized view with around 11M rows.

We are using a rather old version of Postgres (9.6) so some newer features are not available to us in the mean time.

The search_text field is a concatenation of multiple columns (like username, first name, and last_name).

I already have a trigram index on search_text for the similarity search.

I'm looking for advice on how to maintain performance while still being able to sort by the combined score. Any insights or recommendations would be greatly appreciated!

Answer 1

Why does the ORDER BY clause slow down the query so much?

Because the LIMIT clause is applied last.

• Without ORDER BY:
  The query can take the first 100 rows, regardless of their contents, and return the data from them.
• With ORDER BY:
  The query must process the entire table, locate those with the highest vales of combined_score and then return the data from those.

Is there a way to optimize this query while keeping the ORDER BY clause? Would adding an index on the computed score help, and if so, how should I approach that?

You can't add an index on a computed value (in a query), but [I think] you can on a computed column.