Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

create view data as 
select 'a' result_column, 3 order_column  
union all
select 'b', 2 
union all
select 'c' , 1  -- pay attention to order_column value in this example and the used aggregation
union all
select 'c' , 4  -- pay attention to order_column value in this example and the used aggregation
  
;

CREATE VIEW

Select * from data;

result_column
order_column

a
3

b
2

c
1

c
4

SELECT 4

-- throws an error
Select distinct result_column from data
 order by order_column
-- if this would work, the exact output order would be undefined

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 3:  order by order_column
                  ^

-- try to achieve the desired result 
Select result_column from data
 group by result_column 
 order by min(order_column) 
-- the result depends on order_column value and aggregate operator

result_column

c

b

a

SELECT 3