From: =?iso-8859-1?Q?Andr=E9_H=E4nsel?= <andre@webkr.de>
To: <pgsql-performance@lists.postgresql.org>
Subject: Why is there a Sort after an Index Only Scan?
Date: Thu, 5 May 2022 01:15:43 +0200
Message-ID: <014601d8600c$e15aadc0$a4100940$@webkr.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Thread-Index: AdhgDEQkr9NcbGG7TO+352BgFMIqEg==
Content-Language: de
Archived-At: 
 <https://www.postgresql.org/message-id/014601d8600c%24e15aadc0%24a4100940%24%40webkr.de>
Precedence: bulk

Quick(?) question... why is there a Sort node after an Index Only Scan?
Shouldn't the index already spit out sorted tuples?

CREATE INDEX ON orders_test(shipping_date, order_id);

EXPLAIN ANALYZE SELECT
FROM orders_test
WHERE TRUE
AND shipping_date >= '2022-05-01'
AND shipping_date <= '2022-05-01'
ORDER BY order_id
LIMIT 50;

Limit  (cost=8.46..8.46 rows=1 width=4) (actual time=0.031..0.032 rows=0
loops=1)
  ->  Sort  (cost=8.46..8.46 rows=1 width=4) (actual time=0.025..0.025
rows=0 loops=1)
        Sort Key: order_id
        Sort Method: quicksort  Memory: 25kB
        ->  Index Only Scan using orders_test_shipping_date_order_id_idx on
orders_test  (cost=0.43..8.45 rows=1 width=4) (actual time=0.017..0.018
rows=0 loops=1)
              Index Cond: ((shipping_date >= '2022-05-01'::date) AND
(shipping_date <= '2022-05-01'::date))
              Heap Fetches: 0

Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7a3bc2421b5de5a2a377bd39b78d1c
d5

I am actually asking because when I skew the distribution a little and
repeat the query I get a rather unfortunate plan:

INSERT INTO orders_test SELECT generate_series(2000001, 2100000),
'2022-05-01';
ANALYZE orders_test;

Limit  (cost=0.43..37.05 rows=50 width=4) (actual time=1186.565..1186.593
rows=50 loops=1)
  ->  Index Scan using orders_test_pkey on orders_test  (cost=0.43..74336.43
rows=101502 width=4) (actual time=1186.562..1186.584 rows=50 loops=1)
        Filter: ((shipping_date >= '2022-05-01'::date) AND (shipping_date <=
'2022-05-01'::date))
        Rows Removed by Filter: 2000000

Postgres here uses the primary key to get the sort order, so I'm wondering
if there is anything about my index that precludes its use for ORDER BY.