Postgres 12

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 12.12 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 table scores 
       (id serial
       ,physical_ed integer 
       ,first_term  integer[]
       ,second_term integer[]
       ) ;

CREATE TABLE


 create or replace 
function array_sum (the_array integer[])
  returns integer
  language sql 
  immutable 
as $$
    select sum(a_element)::integer from (select unnest(the_array) a_element) u;
$$;

CREATE FUNCTION


alter table scores 
      add column total_score integer 
      generated always as 
      (physical_ed + array_sum(first_term) + array_sum(second_term)) 
      stored;

-- alternative you could concatinate the arrays before calling the function       
alter table scores 
      add column total_score_alt integer 
      generated always as 
      (physical_ed + array_sum(first_term || second_term)) 
      stored; 

ALTER TABLE



ALTER TABLE


insert into scores (physical_ed,first_term,second_term)
  values (40,  '{10,23,43}', '{1,5,5,7}')
       , (10,  '{1,2,3,4,5,6}', '{-1,3}');

INSERT 0 2


select * from scores;


id
physical_ed
first_term
second_term
total_score
total_score_alt


1
40
{10,23,43}
{1,5,5,7}
134
134

2
10
{1,2,3,4,5,6}
{-1,3}
33
33



SELECT 2