-- The ordered index-only scan is for the indexes of a table that has rows: a partitioned table's index (also one made ON ONLY, which a partition attached later completes) has no access method routine,
-- and a hash sub-partition of a range partition is such a table read as the relation of a plan. EXPLAIN of min, max and ORDER BY ... LIMIT 1 over them must plan, and give the rows of the leaves.
CREATE EXTENSION pg_sift;
SET sift.async_maintenance = off;
CREATE TABLE op (id int4, d date, pad text) PARTITION BY RANGE (d);
CREATE TABLE op_a PARTITION OF op FOR VALUES FROM ('2024-01-01') TO ('2024-07-01');
CREATE TABLE op_b PARTITION OF op FOR VALUES FROM ('2024-07-01') TO ('2025-01-01') PARTITION BY HASH (id);
CREATE TABLE op_b0 PARTITION OF op_b FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE op_b1 PARTITION OF op_b FOR VALUES WITH (MODULUS 2, REMAINDER 1);
INSERT INTO op SELECT i, date '2024-01-01' + (i * 7) % 360, 'x' FROM generate_series(1, 3000) i;
CREATE INDEX op_i ON ONLY op USING sift_col (id, d);
CREATE INDEX op_a_i ON op_a USING sift_col (id, d);
NOTICE:  sift_col index "op_a_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('op_a_i');
CREATE INDEX op_b_i ON ONLY op_b USING sift_col (id, d);
CREATE INDEX op_b0_i ON op_b0 USING sift_col (id, d);
NOTICE:  sift_col index "op_b0_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('op_b0_i');
CREATE INDEX op_b1_i ON op_b1 USING sift_col (id, d);
NOTICE:  sift_col index "op_b1_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('op_b1_i');
ALTER INDEX op_b_i ATTACH PARTITION op_b0_i;
ALTER INDEX op_b_i ATTACH PARTITION op_b1_i;
ALTER INDEX op_i ATTACH PARTITION op_a_i;
ALTER INDEX op_i ATTACH PARTITION op_b_i;
VACUUM ANALYZE op;
EXPLAIN (COSTS OFF) SELECT max(id) FROM op_b;
              QUERY PLAN              
--------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on op_b0 op_b_1
         ->  Seq Scan on op_b1 op_b_2
(4 rows)

SELECT max(id), min(id) FROM op_b;
 max  | min 
------+-----
 2982 |  26
(1 row)

SELECT max(id), min(id) FROM op;
 max  | min 
------+-----
 3000 |   1
(1 row)

SELECT id FROM op_b ORDER BY id LIMIT 3;
 id 
----
 26
 27
 28
(3 rows)

SELECT id FROM op ORDER BY id DESC LIMIT 3;
  id  
------
 3000
 2999
 2998
(3 rows)

-- the leaves keep their ordered scans: the parent's plan is a merge of them
SET enable_seqscan = off;
SET enable_sort = off;
SET enable_bitmapscan = off;
EXPLAIN (COSTS OFF) SELECT id FROM op ORDER BY id;
                    QUERY PLAN                     
---------------------------------------------------
 Merge Append
   Sort Key: op.id
   ->  Index Only Scan using op_a_i on op_a op_1
   ->  Index Only Scan using op_b0_i on op_b0 op_2
   ->  Index Only Scan using op_b1_i on op_b1 op_3
(5 rows)

RESET enable_seqscan;
RESET enable_sort;
RESET enable_bitmapscan;
-- an index that is not valid yet (CONCURRENTLY failed, or ON ONLY on a table whose partitions are not attached)
CREATE TABLE ov (id int4, d date) PARTITION BY HASH (id);
CREATE TABLE ov0 PARTITION OF ov FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE ov1 PARTITION OF ov FOR VALUES WITH (MODULUS 2, REMAINDER 1);
INSERT INTO ov SELECT i, date '2024-01-01' + i % 300 FROM generate_series(1, 1000) i;
CREATE INDEX ov_i ON ONLY ov USING sift_col (id, d);
SELECT max(id), min(id) FROM ov;
 max  | min 
------+-----
 1000 |   1
(1 row)

SELECT id FROM ov ORDER BY id LIMIT 2;
 id 
----
  1
  2
(2 rows)

-- the parent of an inheritance tree: its index holds the parent's own rows only, the children's rows come from their own scans
CREATE TABLE oi (id int4, d date);
CREATE TABLE oi_c () INHERITS (oi);
INSERT INTO oi VALUES (5, '2024-01-01'), (1, '2024-01-02');
INSERT INTO oi_c SELECT i, date '2024-02-01' + i FROM generate_series(10, 40) i;
CREATE INDEX oi_i ON oi USING sift_col (id, d);
NOTICE:  sift_col index "oi_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('oi_i');
CREATE INDEX oi_c_i ON oi_c USING sift_col (id, d);
NOTICE:  sift_col index "oi_c_i": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('oi_c_i');
VACUUM ANALYZE oi;
SELECT max(id), min(id) FROM oi;
 max | min 
-----+-----
  40 |   1
(1 row)

SELECT id FROM oi ORDER BY id LIMIT 4;
 id 
----
  1
  5
 10
 11
(4 rows)

SELECT id FROM oi ORDER BY id DESC LIMIT 3;
 id 
----
 40
 39
 38
(3 rows)

DROP TABLE oi_c, oi, op, ov;
DROP EXTENSION pg_sift CASCADE;