Postgres 11

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 TABLE tbl(tbl_id int, arr timestamp[]);

INSERT INTO tbl VALUES
   (1, '{"2012-10-09 17:04:05.710887"
        ,"2013-10-18 22:30:08.973749"
        ,"2014-10-22 22:18:18.885973"}')
 , (2, '{"2012-11-09 17:04:05.710887"
        ,"2013-10-18 22:30:08.973749"
        ,"2014-09-22 22:18:18.885973"}');
        
2 rows affected

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT avg(a2.ts - a1.ts) AS avg_intv
   FROM   unnest(t.arr) WITH ORDINALITY a1(ts, ord)
   JOIN   unnest(t.arr) WITH ORDINALITY a2(ts, ord) ON (a2.ord = a1.ord +1)
   ) avg ON true;
   


tbl_id
arr
avg_intv


1
{"2012-10-09 17:04:05.710887","2013-10-18 22:30:08.973749","2014-10-22 22:18:18.885973"}
371 days 14:37:06.587543

2
{"2012-11-09 17:04:05.710887","2013-10-18 22:30:08.973749","2014-09-22 22:18:18.885973"}
340 days 26:37:06.587543


-- alternative with CTE
SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (
   WITH   a AS (SELECT * FROM unnest(t.arr) WITH ORDINALITY a1(ts, ord))
   SELECT avg(a2.ts - a1.ts) AS avg_intv
   FROM   a a1
   JOIN   a a2 ON (a2.ord = a1.ord +1)
   ) avg ON true;
   


tbl_id
arr
avg_intv


1
{"2012-10-09 17:04:05.710887","2013-10-18 22:30:08.973749","2014-10-22 22:18:18.885973"}
371 days 14:37:06.587543

2
{"2012-11-09 17:04:05.710887","2013-10-18 22:30:08.973749","2014-09-22 22:18:18.885973"}
340 days 26:37:06.587543

tbl_id	arr	avg_intv
1	{"2012-10-09 17:04:05.710887","2013-10-18 22:30:08.973749","2014-10-22 22:18:18.885973"}	371 days 14:37:06.587543
2	{"2012-11-09 17:04:05.710887","2013-10-18 22:30:08.973749","2014-09-22 22:18:18.885973"}	340 days 26:37:06.587543