Postgres 10

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 jockeys
    ("id" int, "date" timestamp, "position" int, "name" varchar(9))
;
    
INSERT INTO jockeys
    ("id", "date", "position", "name")
VALUES
    (1, '2016-06-29 00:00:00', 9, 'Ben Smith'),
    (2, '2016-06-29 00:00:00', 1, 'Ben Smith'),
    (3, '2016-06-29 00:00:00', 5, 'Ben Smith'),
    (4, '2016-06-29 00:00:00', 6, 'Ben Smith'),
    (5, '2016-06-30 00:00:00', 2, 'Ben Smith'),
    (6, '2016-06-30 00:00:00', 2, 'Tom Brown'),
    (7, '2016-06-29 00:00:00', 4, 'Tom Brown'),
    (8, '2016-06-30 00:00:00', 2, 'Tom Brown'),
    (9, '2016-06-30 00:00:00', 1, 'Tom Brown')
;
9 rows affected

CREATE OR REPLACE FUNCTION _final_median(NUMERIC[])
   RETURNS NUMERIC AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;
 
CREATE AGGREGATE median(NUMERIC) (
  SFUNC=array_append,
  STYPE=NUMERIC[],
  FINALFUNC=_final_median,
  INITCOND='{}'
);
SELECT 
j.* ,  array_agg(position) over w as previous_positions,
       median(position) over w_20 as med_20
FROM jockeys j
WINDOW w as
(  partition by name ORDER BY id rows between 
     unbounded preceding and 1 preceding
     ),
     w_20 as
     (  partition by name ORDER BY id rows between 
           20 preceding and 1 preceding
     )


id
date
position
name
previous_positions
med_20


1
2016-06-29 00:00:00
9
Ben Smith
null
null

2
2016-06-29 00:00:00
1
Ben Smith
{9}
9.0000000000000000

3
2016-06-29 00:00:00
5
Ben Smith
{9,1}
5.0000000000000000

4
2016-06-29 00:00:00
6
Ben Smith
{9,1,5}
5.0000000000000000

5
2016-06-30 00:00:00
2
Ben Smith
{9,1,5,6}
5.5000000000000000

6
2016-06-30 00:00:00
2
Tom Brown
null
null

7
2016-06-29 00:00:00
4
Tom Brown
{2}
2.0000000000000000

8
2016-06-30 00:00:00
2
Tom Brown
{2,4}
3.0000000000000000

9
2016-06-30 00:00:00
1
Tom Brown
{2,4,2}
2.0000000000000000

id	date	position	name	previous_positions	med_20
1	2016-06-29 00:00:00	9	Ben Smith	null	null
2	2016-06-29 00:00:00	1	Ben Smith	{9}	9.0000000000000000
3	2016-06-29 00:00:00	5	Ben Smith	{9,1}	5.0000000000000000
4	2016-06-29 00:00:00	6	Ben Smith	{9,1,5}	5.0000000000000000
5	2016-06-30 00:00:00	2	Ben Smith	{9,1,5,6}	5.5000000000000000
6	2016-06-30 00:00:00	2	Tom Brown	null	null
7	2016-06-29 00:00:00	4	Tom Brown	{2}	2.0000000000000000
8	2016-06-30 00:00:00	2	Tom Brown	{2,4}	3.0000000000000000
9	2016-06-30 00:00:00	1	Tom Brown	{2,4,2}	2.0000000000000000