On Thu, 5 May 2022 at 11:15, André Hänsel <andre@xxxxxxxx> wrote: > > 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; Unfortunately, the query planner is not quite smart enough to realise that your shipping_date clauses can only match a single value. There's quite a bit more we could do with the planner's EquivalanceClasses. There is a patch around to help improve things in this area but it requires some more infrastructure to make it more practical to do from a performance standpoint in the planner. You'll get the plan you want if you requite the query and replace your date range with shipping_date = '2022-05-01'. Your use of WHERE TRUE indicates to me that you might be building this query in an application already, 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. David [1] https://commitfest.postgresql.org/38/3524/
