Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE testdata (
 question_id   TEXT        NOT NULL PRIMARY KEY,
 answerer_id   TEXT        NOT NULL,
 question_date TIMESTAMPTZ NOT NULL,
 answer_date   TIMESTAMPTZ NOT NULL
);

INSERT INTO testdata(question_id, answerer_id, question_date, answer_date)
SELECT concat('question_', lpad(i::text, 4, '0')),
       concat('answerer_', lpad(floor(random() * 99 + 1)::text, 2, '0')),
       timestamptz '2021-01-01' + random() * INTERVAL '365 days',
       timestamptz '2022-01-01' + random() * INTERVAL '365 days'
FROM generate_series(1, 9999) AS t(i);

CREATE INDEX idx1 ON testdata (answerer_id, answer_date, question_date);
9999 rows affected

VACUUM ANALYZE testdata;
EXPLAIN ANALYZE
SELECT *
FROM   testdata
WHERE  answerer_id = 'answerer_09'
ORDER  BY answer_date, question_date;


QUERY PLAN


Sort  (cost=108.49..108.75 rows=106 width=42) (actual time=0.168..0.174 rows=106 loops=1)

  Sort Key: answer_date, question_date

  Sort Method: quicksort  Memory: 33kB

  ->  Bitmap Heap Scan on testdata  (cost=5.11..104.92 rows=106 width=42) (actual time=0.045..0.136 rows=106 loops=1)

        Recheck Cond: (answerer_id = 'answerer_09'::text)

        Heap Blocks: exact=61

        ->  Bitmap Index Scan on idx1  (cost=0.00..5.08 rows=106 width=0) (actual time=0.034..0.035 rows=106 loops=1)

              Index Cond: (answerer_id = 'answerer_09'::text)

Planning Time: 0.245 ms

Execution Time: 0.206 ms


-- "force" idx scan
SET enable_bitmapscan = off;
SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT *
FROM   testdata
WHERE  answerer_id = 'answerer_09'
ORDER  BY answer_date, question_date;


QUERY PLAN


Index Scan using idx1 on testdata  (cost=0.29..278.08 rows=106 width=42) (actual time=0.014..0.050 rows=106 loops=1)

  Index Cond: (answerer_id = 'answerer_09'::text)

Planning Time: 0.051 ms

Execution Time: 0.065 ms


-- reset
RESET enable_bitmapscan;
RESET enable_seqscan;

EXPLAIN ANALYZE
SELECT *
FROM   testdata
WHERE  answerer_id = 'answerer_09'
ORDER  BY answer_date, question_date;


QUERY PLAN


Sort  (cost=108.49..108.75 rows=106 width=42) (actual time=0.091..0.096 rows=106 loops=1)

  Sort Key: answer_date, question_date

  Sort Method: quicksort  Memory: 33kB

  ->  Bitmap Heap Scan on testdata  (cost=5.11..104.92 rows=106 width=42) (actual time=0.024..0.067 rows=106 loops=1)

        Recheck Cond: (answerer_id = 'answerer_09'::text)

        Heap Blocks: exact=61

        ->  Bitmap Index Scan on idx1  (cost=0.00..5.08 rows=106 width=0) (actual time=0.016..0.016 rows=106 loops=1)

              Index Cond: (answerer_id = 'answerer_09'::text)

Planning Time: 0.052 ms

Execution Time: 0.118 ms


-- lower expected cost for random access:
SET random_page_cost = 1;

EXPLAIN ANALYZE
SELECT *
FROM   testdata
WHERE  answerer_id = 'answerer_09'
ORDER  BY answer_date, question_date;


QUERY PLAN


Index Scan using idx1 on testdata  (cost=0.29..71.13 rows=106 width=42) (actual time=0.013..0.049 rows=106 loops=1)

  Index Cond: (answerer_id = 'answerer_09'::text)

Planning Time: 0.070 ms

Execution Time: 0.063 ms