From: =?UTF-8?Q?Andr=C3=A9_H=C3=A4nsel?= <andre@webkr.de>
To: "'Pgsql Performance'" <pgsql-performance@lists.postgresql.org>
References: <014601d8600c$e15aadc0$a4100940$@webkr.de>
 <CAApHDvr-apH5h5NpsS=jDPhiGugoBrLz3SbU37xR+1Dg3u7OMQ@mail.gmail.com>
In-Reply-To: 
 <CAApHDvr-apH5h5NpsS=jDPhiGugoBrLz3SbU37xR+1Dg3u7OMQ@mail.gmail.com>
Subject: RE: Why is there a Sort after an Index Only Scan?
Date: Thu, 5 May 2022 03:12:40 +0200
Message-ID: <015101d8601d$37df5190$a79df4b0$@webkr.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQJUs55e01R53ndrs3J+EiO2l5rJFwGrbK3LrAlrVpA=
Content-Language: de
Archived-At: 
 <https://www.postgresql.org/message-id/015101d8601d%2437df5190%24a79df4b0%24%40webkr.de>
Precedence: bulk

> They are sorted by order_id only within sets of the same =
shipping_date, which is not good enough. =20

Ah yes, that totally makes sense for the general case.

> so maybe you can just tweak that application to test if the start and =
end dates are the same and use equality when they are.

I definitely can.

But now I have a followup question, which probably should have been a =
separate question all along. I have modified the example a bit to have a =
more natural date distribution and I got rid of the weird shipping_date =
condition and actually made it different dates, so the index order is =
out of the picture. I also added some statistics so Postgres knows about =
the relationship between the columns.

https://dbfiddle.uk/?rdbms=3Dpostgres_14&fiddle=3D54c7774432e896e3c0e89d8=
084c4b194

After inserting more rows, Postgres still chooses a scan on the primary =
key instead of using the index.

Limit  (cost=3D0.43..296.63 rows=3D50 width=3D4) (actual =
time=3D1052.692..1052.737 rows=3D50 loops=3D1)
  ->  Index Scan using orders_test_pkey on orders_test  =
(cost=3D0.43..71149.43 rows=3D12010 width=3D4) (actual =
time=3D1052.690..1052.728 rows=3D50 loops=3D1)
        Filter: ((shipping_date >=3D '2022-04-30'::date) AND =
(shipping_date <=3D '2022-05-01'::date))
        Rows Removed by Filter: 1998734

By setting the CPU costs to 0 (last block in the fiddle) I can force the =
use of the previous plan and as I already suspected it is much better:

Limit  (cost=3D101.00..101.00 rows=3D50 width=3D4) (actual =
time=3D4.835..4.843 rows=3D50 loops=3D1)
  ->  Sort  (cost=3D101.00..101.00 rows=3D12010 width=3D4) (actual =
time=3D4.833..4.837 rows=3D50 loops=3D1)
        Sort Key: order_id
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Index Scan using orders_test_shipping_date_idx on =
orders_test  (cost=3D0.00..101.00 rows=3D12010 width=3D4) (actual =
time=3D0.026..3.339 rows=3D11266 loops=3D1)
              Index Cond: ((shipping_date >=3D '2022-04-30'::date) AND =
(shipping_date <=3D '2022-05-01'::date))

Is it overestimating the cost of the sorting?