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

CREATE TABLE tbl (device text, col2 numeric, col3 numeric, ts time);
    
INSERT INTO tbl VALUES
 ('cpu1', '3.2', '1.1', '01:45:00'),
 ('cpu1', '3.5', '1.3', '01:40:00'),
 ('cpu1', '5.9', '0.1', '01:35:00'),
 ('cpu1', '1.7', '1.2', '01:30:00'),
 ('cpu1', '2.5', '2.3', '01:25:00'),
 ('cpu1', '5.2', '4.1', '01:20:00'),
 ('cpu3', '4.2', '1.1', '01:45:00'),
 ('cpu3', '3.1', '1.0', '01:40:00'),
 ('cpu3', '5.0', '3.3', '01:35:00'),
 ('cpu3', '4.7', '3.2', '01:30:00'),
 ('cpu3', '2.7', '2.1', '01:25:00'),
 ('cpu3', '6.2', '4.8', '01:20:00')
;

CREATE TABLE



INSERT 0 12


-- for all devices
SELECT *
FROM  (
   SELECT *
        , col2 > max(col2) OVER (PARTITION BY device ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) OR
          col3 < min(col3) OVER (PARTITION BY device ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) AS bingo
   FROM   tbl
   ) sub
WHERE  bingo;


device
col2
col3
ts
bingo


cpu1
5.9
0.1
01:35:00
t

cpu3
6.2
4.8
01:20:00
t

cpu3
3.1
1.0
01:40:00
t



SELECT 3


-- for a single given device
SELECT *
FROM  (
   SELECT *
        , col2 > max(col2) OVER (ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) OR
          col3 < min(col3) OVER (ORDER BY ts ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING EXCLUDE CURRENT ROW) AS bingo
   FROM   tbl
   WHERE  device = 'cpu3'
   ) sub
WHERE  bingo;


device
col2
col3
ts
bingo


cpu3
6.2
4.8
01:20:00
t

cpu3
3.1
1.0
01:40:00
t



SELECT 2