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?.
select version();
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table priorities (
priority1 varchar(1),
priority2 varchar(1),
priority3 varchar(1)
);
insert into priorities values
('a', 'a', null),
('a', null, null),
(null, null, null),
('a', null, 'a'),
('a', 'a', null),
(null, 'a', 'a'),
(null, null, 'a'),
(null, 'a', 'a');
8 rows affected
select
*,
dense_rank() over (
order by priority1 desc nulls last,
priority2 desc nulls last,
priority3 desc nulls last
) priority_rank
from priorities
| priority1 | priority2 | priority3 | priority_rank |
|---|---|---|---|
| a | a | null | 1 |
| a | a | null | 1 |
| a | null | a | 2 |
| a | null | null | 3 |
| null | a | a | 4 |
| null | a | a | 4 |
| null | null | a | 5 |
| null | null | null | 6 |