Why is there a Sort after an Index Only Scan?

From: André Hänsel <andre(at)webkr(dot)de>
To: <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Why is there a Sort after an Index Only Scan?
Date: 2022-05-04 23:15:43
Message-ID: 014601d8600c$e15aadc0$a4100940$@webkr.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2022-05-04 23:37:08 Re: Why is there a Sort after an Index Only Scan?
Previous Message Jeff Janes 2022-05-04 21:56:27 Re: Window partial fetch optimization