Postgres 9.6

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

-- your setup:
CREATE TABLE tbl (mac text, sn text, date date, time time, loc text, vin1 numeric, vin2 numeric, vin3 numeric);
    
INSERT INTO tbl (mac, sn, time, date, loc, vin1, vin2, vin3)
VALUES
    ('1a34', '4as11111111',  '07:06:18', '2018-01-01', 'aaaa', 447.42, 472.32, 682.59),
    ('1a34', '4as11111111',  '07:06:18', '2018-01-01', 'aaaa', 447.42, 472.32, 682.59),
    ('1a34', '4as11111111',  '07:07:18', '2018-01-01', 'aaaa', 446.42, 477.32, 882.59),
    ('1a34', '4as11111111',  '07:08:28', '2018-01-01', 'aaaa', 457.42, 572.32, 482.59),
    ('1a34', '4as11111111',  '07:09:38', '2018-01-01', 'aaaa', 427.42, 372.32, 582.59),
    ('1a34', '4as11111111',  '07:10:15', '2018-01-01', 'aaaa', 417.42, 472.32, 682.59),
    ('1a34', '4as11111111',  '07:11:18', '2018-01-01', 'aaaa', 467.42, 672.32, 282.59),
    ('1a34', '4as11111111',  '07:12:12', '2018-01-01', 'aaaa', 457.42, 472.32, 382.59),
    ('1a34', '4as11111111',  '07:13:18', '2018-01-01', 'aaaa', 447.42, 572.32, 582.59),
    ('1a34', '4as11111111',  '07:14:10', '2018-01-01', 'aaaa', 437.42, 272.32, 682.59),
    ('1a34', '4as11111111',  '07:15:18', '2018-01-01', 'aaaa', 467.42, 472.32, 682.59),
    ('1a34', '4as11111111',  '07:16:58', '2018-01-01', 'aaaa', 477.42, 372.32, 782.59);

INSERT 0 12


-- query
SELECT t.mac, grid.sn, grid.ts::time AS time, grid.ts::date AS date
     , t.loc, t.vin1_av, t.vin2_av, t.vin3_av
FROM  (
   SELECT sn, generate_series(min(date+time)  -- generate timestamp on the fly
                            , max(date+time)
                            , interval '5 min') AS ts
   FROM   tbl
   WHERE  sn = '4as11111111'
   AND    date+time >= '2018-01-01 0:0'
   AND    date+time <  '2018-01-02 0:0'
   GROUP  BY 1
   ) grid
CROSS JOIN LATERAL (
   SELECT mac, loc
        , round(avg(vin1)::numeric, 2) AS vin1_av  -- cast to numeric for round()
        , round(avg(vin2)::numeric, 2) AS vin2_av  -- but rounding is optional
        , round(avg(vin3)::numeric, 2) AS vin3_av
   FROM   tbl
   WHERE  sn = grid.sn
   AND    date+time >= grid.ts
   AND    date+time <  grid.ts + interval '5 min'
   GROUP  BY mac, loc
   HAVING count(*) > 0  -- exclude empty slots
   ) t;


mac
sn
time
date
loc
vin1_av
vin2_av
vin3_av


1a34
4as11111111
07:06:18
2018-01-01
aaaa
440.59
473.15
665.92

1a34
4as11111111
07:11:18
2018-01-01
aaaa
455.42
492.32
522.59

1a34
4as11111111
07:16:18
2018-01-01
aaaa
477.42
372.32
782.59



SELECT 3


-- cleaner with generate_series in FROM list:
SELECT t.mac, sn.sn, t.loc, ts.ts::time AS time, ts.ts::date AS date
     , t.vin1_av, t.vin2_av, t.vin3_av
FROM  (SELECT text '4as11111111') sn(sn)  -- provide sn here once
CROSS  JOIN LATERAL (
   SELECT min(date+time) AS min_ts, max(date+time) AS max_ts
   FROM   tbl
   WHERE  sn = sn.sn
   AND    date+time >= '2018-01-01 0:0'   -- provide time frame here
   AND    date+time <  '2018-01-02 0:0'
   ) grid
CROSS  JOIN LATERAL generate_series(min_ts, max_ts, interval '5 min') ts(ts)
CROSS  JOIN LATERAL (
   SELECT mac, loc
        , round(avg(vin1)::numeric, 2) AS vin1_av  -- cast to numeric for round()
        , round(avg(vin2)::numeric, 2) AS vin2_av  -- but rounding is optional
        , round(avg(vin3)::numeric, 2) AS vin3_av
   FROM   tbl
   WHERE  sn = sn.sn
   AND    date+time >= ts.ts
   AND    date+time <  ts.ts + interval '5 min'
   GROUP  BY mac, loc
   HAVING count(*) > 0  -- exclude empty slots
   ) t;


mac
sn
loc
time
date
vin1_av
vin2_av
vin3_av


1a34
4as11111111
aaaa
07:06:18
2018-01-01
440.59
473.15
665.92

1a34
4as11111111
aaaa
07:11:18
2018-01-01
455.42
492.32
522.59

1a34
4as11111111
aaaa
07:16:18
2018-01-01
477.42
372.32
782.59



SELECT 3

mac	sn	time	date	loc	vin1_av	vin2_av	vin3_av
1a34	4as11111111	07:06:18	2018-01-01	aaaa	440.59	473.15	665.92
1a34	4as11111111	07:11:18	2018-01-01	aaaa	455.42	492.32	522.59
1a34	4as11111111	07:16:18	2018-01-01	aaaa	477.42	372.32	782.59