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)
);
INSERT INTO orders_test SELECT generate_series(1, 2000000), '2018-01-01'::timestamp + random() * ('2022-05-01'::timestamp - '2018-01-01'::timestamp);
ANALYZE orders_test;

DROP TABLE

CREATE TABLE

INSERT 0 2000000

ANALYZE

CREATE INDEX ON orders_test(shipping_date, order_id);

CREATE INDEX

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;

QUERY PLAN

Limit  (cost=8.46..8.46 rows=1 width=4) (actual time=0.182..0.183 rows=0 loops=1)

  ->  Sort  (cost=8.46..8.46 rows=1 width=4) (actual time=0.037..0.038 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.020..0.020 rows=0 loops=1)

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

              Heap Fetches: 0

Planning Time: 1.769 ms

Execution Time: 0.542 ms

EXPLAIN

INSERT INTO orders_test SELECT generate_series(2000001, 2100000), '2022-05-01';
ANALYZE orders_test;

INSERT 0 100000

ANALYZE

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;

QUERY PLAN

Limit  (cost=0.43..37.88 rows=50 width=4) (actual time=835.385..835.421 rows=50 loops=1)

  ->  Index Scan using orders_test_pkey on orders_test  (cost=0.43..74336.43 rows=99259 width=4) (actual time=835.382..835.410 rows=50 loops=1)

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

        Rows Removed by Filter: 2000000

Planning Time: 0.748 ms

Execution Time: 835.456 ms

EXPLAIN