Why doesn't the second query use the index for sorting?

[Alf Lervåg]

I’m trying to get a query to use the index for sorting. As far as I can 
understand it should be possible. Since you’re reading this you’ve probably 
guessed that I’m stuck.

I’ve boiled down my issue to the script below. Note that my real query needs 
about 80MB for the quick sort. The version using the index for sorting runs in 
about 300ms while the version that sorts uses about 700ms.

Does anyone have a good explanation for why the two queries behave differently 
and if there is something I can do to get rid of the memory sort?

I’m running this on PostgreSQL 10.3 on x86_64-apple-darwin16.7.0, compiled by 
Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit. Let me know if you need to 
know any configuration options.

— 
Thank you,
Alf Lervåg


BEGIN;
CREATE TABLE reading  (
       reading_id integer NOT NULL,
       datetime timestamp with time zone NOT NULL,
       value double precision NOT NULL);

INSERT INTO reading (reading_id, datetime, value)
   SELECT reading_id, datetime, (random() - 0.9) * 100
   FROM generate_series('2016-01-01 00:00Z'::timestamptz, CURRENT_TIMESTAMP, '5 
min') a(datetime)
   CROSS JOIN generate_series(1, 100, 1) b(reading_id);

ALTER TABLE reading ADD PRIMARY KEY (reading_id, datetime);
ANALYZE reading;

EXPLAIN ANALYZE
SELECT reading_id, datetime, value
FROM reading WHERE reading_id IN (176, 155, 156)
ORDER BY reading_id, datetime;

                                                      QUERY PLAN
Index Scan using reading_pkey on reading  (cost=0.56..5.72 rows=1 width=20) 
(actual time=0.044..0.044 rows=0 loops=1)
  Index Cond: (reading_id = ANY ('{176,155,156}'::integer[]))
Planning time: 0.195 ms
Execution time: 0.058 ms
(4 rows)

EXPLAIN ANALYZE
SELECT reading_id, datetime, value
FROM reading WHERE reading_id IN (VALUES (176), (155), (156))
ORDER BY reading_id, datetime;

                                                                 QUERY PLAN
Sort  (cost=250704.99..252542.72 rows=735093 width=20) (actual 
time=0.030..0.030 rows=0 loops=1)
  Sort Key: reading.reading_id, reading.datetime
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop  (cost=0.61..179079.12 rows=735093 width=20) (actual 
time=0.026..0.026 rows=0 loops=1)
        ->  HashAggregate  (cost=0.05..0.08 rows=3 width=4) (actual 
time=0.006..0.007 rows=3 loops=1)
              Group Key: "*VALUES*".column1
              ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=4) 
(actual time=0.001..0.002 rows=3 loops=1)
        ->  Index Scan using reading_pkey on reading  (cost=0.56..57242.70 
rows=245031 width=20) (actual time=0.005..0.005 rows=0 loops=3)
              Index Cond: (reading_id = "*VALUES*".column1)
Planning time: 0.162 ms
Execution time: 0.062 ms
(11 rows)

ROLLBACK;