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?.
create table dummy (
col_a varchar(10),
col_b varchar(10),
col_c varchar(10),
test integer
);
insert into dummy values
('A', 'B', 'C', 1),
('B', 'C', 'A', 2),
('C', 'A', 'B', 3),
('D', 'E', 'F', 4);
SELECT * FROM dummy;
4 rows affected
| col_a | col_b | col_c | test |
|---|---|---|---|
| A | B | C | 1 |
| B | C | A | 2 |
| C | A | B | 3 |
| D | E | F | 4 |
WITH cte AS ( SELECT col_a, col_b, col_c, test,
ROW_NUMBER() OVER (ORDER BY col_b) rn
FROM dummy )
SELECT col_a, col_b, col_c, test
FROM cte
WHERE rn > ( SELECT rn
FROM cte
WHERE (col_a, col_b, col_c, test) = ('A', 'B', 'C', 1 ) )
ORDER BY rn;
| col_a | col_b | col_c | test |
|---|---|---|---|
| B | C | A | 2 |
| D | E | F | 4 |