Postgres 16

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 extension intarray;

CREATE EXTENSION


create table stats
(
	id serial,
	a	int4,
    b	int4,
    c 	int4,
    d 	int4,
    e	int4
);

insert into stats(a,b,c,d,e) values 
(6,3,3,null,null), 
(5,6,null,null,null), 
(4,5,6,null,null),
(11,7,8,null,null);

CREATE TABLE



INSERT 0 4


create or replace function median(variadic arr integer[]) returns decimal as $$	
declare	
	array_sorted int[];
	len int;
begin
	array_sorted = sort(array_remove(arr, null));
	len = array_length(array_sorted, 1);	 
	if mod(len, 2) != 0 then
		return array_sorted[(len+1)/2];
	else 
		return (array_sorted[(len+1)/2] + array_sorted[(len+1)/2+1]) / 2::decimal;
	end if;	
end; 
$$ language plpgsql;

CREATE FUNCTION


select id, median(a,b,c,d,e) from stats order by id;


id
median


1
3

2
5.5000000000000000

3
5

4
8



SELECT 4