From: André Hänsel <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> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andre@webkr.de, pgsql-performance@lists.postgresql.org
Subject: Re: Why is there a Sort after an Index Only Scan?
In-Reply-To: <014601d8600c$e15aadc0$a4100940$@webkr.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox