Postgres 12

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

select version();


version


PostgreSQL 12.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


CREATE TABLE data 
(
  s SERIAL PRIMARY KEY, 
  t TIMESTAMP, 
  lat NUMERIC
);

CREATE TABLE


INSERT INTO data (t, lat)
VALUES
('2019-01-01 00:00:00', 5.07),
('2019-01-01 01:00:00', 4.60),
('2019-01-01 02:00:00', NULL),
('2019-01-01 03:00:00', NULL),
('2019-01-01 04:00:00', 4.7),
('2019-01-01 05:00:00', 4.20),
('2019-01-01 06:00:00', NULL),
('2019-01-01 07:00:00', 4.98),
('2019-01-01 08:00:00', 4.50),
('2019-01-01 09:00:00', 4.7),
('2019-01-01 10:00:00', NULL),
('2019-01-01 11:00:00', NULL),
('2019-01-01 12:00:00', NULL),
('2019-01-01 13:00:00', 6.45),
('2019-01-01 14:00:00', 3.50);

INSERT 0 15


WITH cte1 AS
(
  SELECT d1.s,
    d1.t AS t1, d1.lat AS l1,
    LAG(d1.lat, 3)  OVER (ORDER BY t ASC) AS lat_t1_3,
    LAG(d1.lat, 2)  OVER (ORDER BY t ASC) AS lag_t1_2,
    LAG(d1.lat, 1)  OVER (ORDER BY t ASC) AS lag_t1,
    LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
    LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
  FROM data d1
),
cte2 AS
(
  SELECT 
    d2.t AS t2, d2.lat AS l2, 
    LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
    LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
  FROM data d2
),
cte3 AS
(
  SELECT t1.s,
    t1.t1,  t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2, 
    t1.lead_t1, t2.lead_t2, t1.lead_t1_2
  FROM cte1 t1
  JOIN cte2 t2
  ON t1.t1 = t2.t2
)
SELECT * FROM cte3;


s
t1
lag_t1_2
lag_t1
lag_t2
l1
l2
lead_t1
lead_t2
lead_t1_2


1
2019-01-01 00:00:00
null
null
4.60
5.07
5.07
4.60
null
null

2
2019-01-01 01:00:00
null
5.07
null
4.60
4.60
null
5.07
null

3
2019-01-01 02:00:00
5.07
4.60
null
null
null
null
4.60
4.7

4
2019-01-01 03:00:00
4.60
null
4.7
null
null
4.7
null
4.20

5
2019-01-01 04:00:00
null
null
4.20
4.7
4.7
4.20
null
null

6
2019-01-01 05:00:00
null
4.7
null
4.20
4.20
null
4.7
4.98

7
2019-01-01 06:00:00
4.7
4.20
4.98
null
null
4.98
4.20
4.50

8
2019-01-01 07:00:00
4.20
null
4.50
4.98
4.98
4.50
null
4.7

9
2019-01-01 08:00:00
null
4.98
4.7
4.50
4.50
4.7
4.98
null

10
2019-01-01 09:00:00
4.98
4.50
null
4.7
4.7
null
4.50
null

11
2019-01-01 10:00:00
4.50
4.7
null
null
null
null
4.7
null

12
2019-01-01 11:00:00
4.7
null
null
null
null
null
null
6.45

13
2019-01-01 12:00:00
null
null
6.45
null
null
6.45
null
3.50

14
2019-01-01 13:00:00
null
null
3.50
6.45
6.45
3.50
null
null

15
2019-01-01 14:00:00
null
6.45
null
3.50
3.50
null
6.45
null



SELECT 15


WITH cte1 AS
(
  SELECT d1.s,
    d1.t AS t1, d1.lat AS l1,
    LAG(d1.lat, 2)  OVER (ORDER BY t ASC) AS lag_t1_2,
    LAG(d1.lat, 1)  OVER (ORDER BY t ASC) AS lag_t1,
    LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
    LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
  FROM data d1
),
cte2 AS
(
  SELECT 
    d2.t AS t2, d2.lat AS l2, 
    LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
    LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
  FROM data d2
),
cte3 AS
(
  SELECT t1.s,
    t1.t1,  t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2, 
    t1.lead_t1, t2.lead_t2, t1.lead_t1_2
  FROM cte1 t1
  JOIN cte2 t2
  ON t1.t1 = t2.t2
),
cte4 AS
(
  SELECT t1.s,
  t1.l1 AS lat,
    CASE 
    
      -- The WHEN for the middle of 3 NULLs has to be at the beginning
      -- of the CASE - if at the end, it remains NULL - why?

      WHEN (t1.lag_t1 IS NULL) AND (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL)  
        AND (t1.lead_t1 IS NULL) AND (t1.lead_t2 IS NULL)
      THEN ROUND((t1.lag_t1_2 + t1.lead_t1_2)/2, 2)

      WHEN (t1.l1 IS NOT NULL) THEN t1.l1
      WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
        AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2) 
      WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL) 
        AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
      WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL)  AND (t1.lag_t1 IS NULL)
        AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
      ELSE 0
    END AS final_val
  FROM cte3 t1
)
SELECT s, lat, final_val FROM cte4;



s
lat
final_val


1
5.07
5.07

2
4.60
4.60

3
null
4.84

4
null
4.45

5
4.7
4.7

6
4.20
4.20

7
null
4.59

8
4.98
4.98

9
4.50
4.50

10
4.7
4.7

11
null
4.60

12
null
5.58

13
null
4.98

14
6.45
6.45

15
3.50
3.50



SELECT 15

s	t1	lag_t1_2	lag_t1	lag_t2	l1	l2	lead_t1	lead_t2	lead_t1_2
1	2019-01-01 00:00:00	null	null	4.60	5.07	5.07	4.60	null	null
2	2019-01-01 01:00:00	null	5.07	null	4.60	4.60	null	5.07	null
3	2019-01-01 02:00:00	5.07	4.60	null	null	null	null	4.60	4.7
4	2019-01-01 03:00:00	4.60	null	4.7	null	null	4.7	null	4.20
5	2019-01-01 04:00:00	null	null	4.20	4.7	4.7	4.20	null	null
6	2019-01-01 05:00:00	null	4.7	null	4.20	4.20	null	4.7	4.98
7	2019-01-01 06:00:00	4.7	4.20	4.98	null	null	4.98	4.20	4.50
8	2019-01-01 07:00:00	4.20	null	4.50	4.98	4.98	4.50	null	4.7
9	2019-01-01 08:00:00	null	4.98	4.7	4.50	4.50	4.7	4.98	null
10	2019-01-01 09:00:00	4.98	4.50	null	4.7	4.7	null	4.50	null
11	2019-01-01 10:00:00	4.50	4.7	null	null	null	null	4.7	null
12	2019-01-01 11:00:00	4.7	null	null	null	null	null	null	6.45
13	2019-01-01 12:00:00	null	null	6.45	null	null	6.45	null	3.50
14	2019-01-01 13:00:00	null	null	3.50	6.45	6.45	3.50	null	null
15	2019-01-01 14:00:00	null	6.45	null	3.50	3.50	null	6.45	null