Optimizing ORDER BY performance in PostgreSQL with hash partitioning on survey_id

Question

I am working with a PostgreSQL query that retrieves survey responses and sorts them by answer_id and option_index. However, the sorting (ORDER BY) is making the query extremely slow due to the large dataset.

SELECT 
    p.phone, s.code, 
    sq.question || CASE 
        WHEN qo.option_value IS NOT NULL THEN ' (' || qo.option_value || ')' 
        ELSE '' 
    END AS question, 
    av.answer_text, av.answer_id, qo.option_index
FROM survey_responses sr
JOIN survey_question sq ON sq.survey_id = sr.survey_id 
JOIN survey s ON sr.survey_id = s.id
JOIN panelist p ON p.id = sr.panelist_id
JOIN survey_answer sa ON sa.survey_response_id = sr.id AND sq.id = sa.question_id
JOIN answer_values av ON av.answer_id = sa.id 
LEFT JOIN question_options qo ON qo.question_id = sq.id AND av.option_index = qo.option_index
ORDER BY av.answer_id, qo.option_index;

The dataset is too large, and sorting is causing slow performance. EXPLAIN ANALYZE shows that PostgreSQL is using quicksort in memory with high memory consumption. If the dataset grows, PostgreSQL falls back to external sorting on disk, making it even slower.

Execution Plan (Excerpt):

Gather Merge  (cost=204177.85..204252.52 rows=640 width=81) (actual time=2513.994..2788.648 rows=676420 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort  (cost=203177.82..203178.62 rows=320 width=81) (actual time=2498.760..2533.608 rows=225473 loops=3)
        Sort Key: av.answer_id, qo.option_index
        Sort Method: quicksort  Memory: 64701kB
        Worker 0:  Sort Method: quicksort  Memory: 62785kB
        Worker 1:  Sort Method: quicksort  Memory: 94267kB

Object definitions:

CREATE INDEX idx_answer_values_order ON answer_values (answer_id, option_index);
SET work_mem = '1024MB';

CREATE TABLE survey_responses (
    id          UUID NOT NULL,
    created_at  TIMESTAMP WITHOUT TIME ZONE,
    updated_at  TIMESTAMP WITHOUT TIME ZONE,
    mongo_id    VARCHAR(255),
    old_id      VARCHAR(255),
    panelist_id UUID NOT NULL,
    survey_id   UUID NOT NULL,
    survey_date DATE,
    PRIMARY KEY (id, survey_id)
) PARTITION BY HASH (survey_id);

CREATE TABLE survey_responses_p0 PARTITION OF survey_responses FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE survey_responses_p1 PARTITION OF survey_responses FOR VALUES WITH (MODULUS 8, REMAINDER 1);