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

[Alf Lervåg <alf@xxxxxxxxxx>]

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;