Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
DROP TABLE IF EXISTS orders_test;
CREATE TABLE orders_test(
    order_id int not null,
    shipping_date date not null,
    PRIMARY KEY (order_id)
);
CREATE STATISTICS foo ON order_id, shipping_date FROM orders_test;
INSERT INTO orders_test SELECT id, '2018-01-01'::date + (id::bigint * 1581 / 2000000)::int FROM generate_series(1,2000000) AS id;
ANALYZE orders_test;
2000000 rows affected

CREATE INDEX ON orders_test(shipping_date);
EXPLAIN ANALYZE SELECT
FROM orders_test
WHERE TRUE
AND shipping_date >= '2022-04-30'
AND shipping_date <= '2022-05-01'
ORDER BY order_id
LIMIT 50;


QUERY PLAN


Limit  (cost=155.86..155.98 rows=50 width=4) (actual time=0.981..0.998 rows=50 loops=1)

  ->  Sort  (cost=155.86..161.94 rows=2432 width=4) (actual time=0.978..0.986 rows=50 loops=1)

        Sort Key: order_id

        Sort Method: top-N heapsort  Memory: 27kB

        ->  Index Scan using orders_test_shipping_date_idx on orders_test  (cost=0.43..75.07 rows=2432 width=4) (actual time=0.049..0.610 rows=1266 loops=1)

              Index Cond: ((shipping_date >= '2022-04-30'::date) AND (shipping_date <= '2022-05-01'::date))

Planning Time: 1.276 ms

Execution Time: 1.053 ms


INSERT INTO orders_test SELECT generate_series(2000001, 2010000), '2022-05-01';
ANALYZE orders_test;
10000 rows affected

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


QUERY PLAN


Limit  (cost=0.43..296.63 rows=50 width=4) (actual time=1052.692..1052.737 rows=50 loops=1)

  ->  Index Scan using orders_test_pkey on orders_test  (cost=0.43..71149.43 rows=12010 width=4) (actual time=1052.690..1052.728 rows=50 loops=1)

        Filter: ((shipping_date >= '2022-04-30'::date) AND (shipping_date <= '2022-05-01'::date))

        Rows Removed by Filter: 1998734

Planning Time: 0.423 ms

Execution Time: 1052.767 ms


SET cpu_tuple_cost = 0;
SET cpu_index_tuple_cost = 0;
SET cpu_operator_cost = 0;
EXPLAIN ANALYZE
SELECT
FROM orders_test
WHERE TRUE
AND shipping_date >= '2022-04-30'
AND shipping_date <= '2022-05-01'
ORDER BY order_id
LIMIT 50;


QUERY PLAN


Limit  (cost=101.00..101.00 rows=50 width=4) (actual time=4.835..4.843 rows=50 loops=1)

  ->  Sort  (cost=101.00..101.00 rows=12010 width=4) (actual time=4.833..4.837 rows=50 loops=1)

        Sort Key: order_id

        Sort Method: top-N heapsort  Memory: 27kB

        ->  Index Scan using orders_test_shipping_date_idx on orders_test  (cost=0.00..101.00 rows=12010 width=4) (actual time=0.026..3.339 rows=11266 loops=1)

              Index Cond: ((shipping_date >= '2022-04-30'::date) AND (shipping_date <= '2022-05-01'::date))

Planning Time: 0.253 ms

Execution Time: 4.868 ms