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?.

--The database stores a table of the following form:

create table your_table ("Parent name", "Start",	"End",	Value) as values
 ('PN1',	12,	16,	2)
,('PN1',	19,	29,	4)
,('PN1',	32,	34,	6)
,('PN2',	4,	17,	3)
,('PN2',	32,	33,	6)
;
--...	...	...	...
/*
Is it possible to calculate the average by grouping the values according to the segment length condition?

That is. if the length of the segment is 30. the regrouped table is as follows:

Parent name	Avg
PN1	(2+4)/2
PN1	6/1
...	...
PN2	3/1
PN2	6/1
...	...
Now I am not considering situations where the value of a segment may overlap with values within cells.

In fact, the task of grouping is to find a way to "collapse" the values.
*/

SELECT 5


select "Parent name", avg(value)::float
from your_table
group by "Parent name", floor("End"/30)::int;


Parent name
avg


PN1
3

PN1
6

PN2
6

PN2
3



SELECT 4


--https://stackoverflow.com/a/78297752/5298879
select "Parent name"
      ,avg(value)
      ,format( '(%s)/%s'
              ,string_agg(value::text,'+')
              ,count(*)) as calculation
from your_table
group by "Parent name", "End"/30
order by "Parent name", max("End");


Parent name
avg
calculation


PN1
3.0000000000000000
(2+4)/2

PN1
6.0000000000000000
(6)/1

PN2
3.0000000000000000
(3)/1

PN2
6.0000000000000000
(6)/1



SELECT 4


select -29/30;


?column?


0



SELECT 1

Parent name	avg	calculation
PN1	3.0000000000000000	(2+4)/2
PN1	6.0000000000000000	(6)/1
PN2	3.0000000000000000	(3)/1
PN2	6.0000000000000000	(6)/1