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

CREATE TABLE conditions (
	"time" timestamptz NOT NULL,
	"location" text NOT NULL,
	temperature float8 NULL,
	humidity float8 NULL
);
insert into conditions values ('2020-06-23 14:03:44', 'office', 70.0,50.0);
insert into conditions values ('2020-06-23 14:03:44', 'home', 68.0,36.0);
insert into conditions values ('2020-06-23 14:03:45', 'office', 70.0,50.0);
insert into conditions values ('2020-06-23 14:03:45', 'home', 68.0,36.0);
insert into conditions values ('2020-06-23 14:03:46', 'office2', 68.0,36.0);
insert into conditions values ('2020-06-23 14:03:48', 'office2', NULL,36.0);
insert into conditions values ('2020-06-23 14:03:51', 'office2', 68.0,NULL);
insert into conditions values ('2020-06-23 14:03:53', 'office2', 72.0,36.0);
insert into conditions values ('2020-06-23 14:03:54', 'office2', NULL,36.0);
insert into conditions values ('2020-06-23 14:03:54', 'office2', NULL,36.0);
insert into conditions values ('2020-07-09 12:11:15', 'office', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:20', 'office', 45.0,NULL);
insert into conditions values ('2020-07-09 12:29:20', 'home', 45.0,NULL);
insert into conditions values ('2020-07-09 12:10:36', 'home', NULL,34.0);
insert into conditions values ('2020-07-09 12:11:16', 'office', NULL,32.0);
insert into conditions values ('2020-07-09 12:09:41', 'home', 60.0,32.0);
insert into conditions values ('2020-07-09 12:11:16', 'home', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:21', 'home', 45.0,NULL);
insert into conditions values ('2020-07-09 12:10:37', 'home', NULL,34.0);
insert into conditions values ('2020-07-09 12:11:24', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:23', 'office2', 45.0,NULL);
insert into conditions values ('2020-07-09 12:10:45', 'office2', NULL,34.0);
insert into conditions values ('2020-07-09 12:11:26', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:28', 'office2', 45.0,NULL);
insert into conditions values ('2020-07-09 12:11:20', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:27', 'office2', 45.0,NULL);
insert into conditions values ('2020-07-09 12:10:42', 'office2', NULL,34.0);
insert into conditions values ('2020-07-09 12:09:44', 'office2', 60.0,32.0);
insert into conditions values ('2020-07-09 12:11:26', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:28', 'office2', 45.0,NULL);
insert into conditions values ('2020-07-09 12:10:40', 'office2', NULL,34.0);
insert into conditions values ('2020-07-09 12:11:18', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:29:30', 'office2', 45.0,NULL);
insert into conditions values ('2020-07-09 12:09:40', 'office2', 60.0,NULL);
insert into conditions values ('2020-07-09 12:09:40', 'home', 60.0,NULL);
insert into conditions values ('2020-07-09 12:09:40', 'office', NULL,18.0);
insert into conditions values ('2020-07-09 12:09:41', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:09:41', 'office2', NULL,32.0);
insert into conditions values ('2020-07-09 12:09:43', 'office2', 68.0,NULL);
insert into conditions values ('2020-07-09 12:09:41', 'office', 65.0,NULL);
insert into conditions values ('2020-07-09 12:10:36', 'office', NULL,45.0);
insert into conditions values ('2020-07-09 12:10:37', 'office', NULL,37.0);
insert into conditions values ('2020-07-09 12:10:38', 'office2', NULL,36.0);
insert into conditions values ('2020-07-09 12:11:15', 'home', 43.0,27.0);
insert into conditions values ('2020-07-09 12:11:23', 'office2', 45.0,32.0);
insert into conditions values ('2020-07-09 12:29:21', 'office', NULL,26.0);
insert into conditions values ('2020-07-09 12:29:26', 'office2', 63.0,27.0);
insert into conditions values ('2020-07-09 12:09:42', 'office2', 72.0,NULL);
insert into conditions values ('2020-07-09 12:10:43', 'office2', 56.0,26.0);
insert into conditions values ('2020-07-09 12:10:44', 'office2', NULL,27.0);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT
  time, location, temperature, humidity,
  first_value(temperature) over (partition by value_partition order by location, time),
  first_value(humidity) over (partition by value_partition1 order by location, time)
FROM (
  SELECT
    time, location, temperature, humidity,
    sum(case when temperature is null then 0 else 1 end) over (order by location, time) as value_partition,
    sum(case when humidity is null then 0 else 1 end) over (order by location, time) as value_partition1

  FROM conditions
  ORDER BY location, time
) as A
order by location, time


time
location
temperature
humidity
first_value
first_value


2020-06-23 14:03:44+01
home
68
36
68
36

2020-06-23 14:03:45+01
home
68
36
68
36

2020-07-09 12:09:40+01
home
60
null
60
36

2020-07-09 12:09:41+01
home
60
32
60
32

2020-07-09 12:10:36+01
home
null
34
60
34

2020-07-09 12:10:37+01
home
null
34
60
34

2020-07-09 12:11:15+01
home
43
27
43
27

2020-07-09 12:11:16+01
home
null
32
43
32

2020-07-09 12:29:20+01
home
45
null
45
32

2020-07-09 12:29:21+01
home
45
null
45
32

2020-06-23 14:03:44+01
office
70
50
70
50

2020-06-23 14:03:45+01
office
70
50
70
50

2020-07-09 12:09:40+01
office
null
18
70
18

2020-07-09 12:09:41+01
office
65
null
65
18

2020-07-09 12:10:36+01
office
null
45
65
45

2020-07-09 12:10:37+01
office
null
37
65
37

2020-07-09 12:11:15+01
office
null
32
65
32

2020-07-09 12:11:16+01
office
null
32
65
32

2020-07-09 12:29:20+01
office
45
null
45
32

2020-07-09 12:29:21+01
office
null
26
45
26

2020-06-23 14:03:46+01
office2
68
36
68
36

2020-06-23 14:03:48+01
office2
null
36
68
36

2020-06-23 14:03:51+01
office2
68
null
68
36

2020-06-23 14:03:53+01
office2
72
36
72
36

2020-06-23 14:03:54+01
office2
null
36
72
36

2020-06-23 14:03:54+01
office2
null
36
72
36

2020-07-09 12:09:40+01
office2
60
null
60
36

2020-07-09 12:09:41+01
office2
null
32
60
32

2020-07-09 12:09:41+01
office2
null
32
60
32

2020-07-09 12:09:42+01
office2
72
null
72
32

2020-07-09 12:09:43+01
office2
68
null
68
32

2020-07-09 12:09:44+01
office2
60
32
60
32

2020-07-09 12:10:38+01
office2
null
36
60
36

2020-07-09 12:10:40+01
office2
null
34
60
34

2020-07-09 12:10:42+01
office2
null
34
60
34

2020-07-09 12:10:43+01
office2
56
26
56
26

2020-07-09 12:10:44+01
office2
null
27
56
27

2020-07-09 12:10:45+01
office2
null
34
56
34

2020-07-09 12:11:18+01
office2
null
32
56
32

2020-07-09 12:11:20+01
office2
null
32
56
32

2020-07-09 12:11:23+01
office2
45
32
45
32

2020-07-09 12:11:24+01
office2
null
32
45
32

2020-07-09 12:11:26+01
office2
null
32
45
32

2020-07-09 12:11:26+01
office2
null
32
45
32

2020-07-09 12:29:23+01
office2
45
null
45
32

2020-07-09 12:29:26+01
office2
63
27
63
27

2020-07-09 12:29:27+01
office2
45
null
45
27

2020-07-09 12:29:28+01
office2
45
null
45
27

2020-07-09 12:29:28+01
office2
45
null
45
27

2020-07-09 12:29:30+01
office2
45
null
45
27


update conditions B
set temperature = C.new_temp

from (

SELECT
  time, location, temperature, humidity,
  first_value(temperature) over (partition by value_partition order by location, time) as new_temp,
  first_value(humidity) over (partition by value_partition1 order by location, time) as new_hum
FROM (
  SELECT
    time, location, temperature, humidity,
    sum(case when temperature is null then 0 else 1 end) over (order by location, time) as value_partition,
    sum(case when humidity is null then 0 else 1 end) over (order by location, time) as value_partition1

  FROM conditions
  ORDER BY location, time ASC
) as A) as C
where C.time = B.time
and C.location = B.location
50 rows affected

select * from conditions order by location, time;


time
location
temperature
humidity


2020-06-23 14:03:44+01
home
68
36

2020-06-23 14:03:45+01
home
68
36

2020-07-09 12:09:40+01
home
60
null

2020-07-09 12:09:41+01
home
60
32

2020-07-09 12:10:36+01
home
60
34

2020-07-09 12:10:37+01
home
60
34

2020-07-09 12:11:15+01
home
43
27

2020-07-09 12:11:16+01
home
43
32

2020-07-09 12:29:20+01
home
45
null

2020-07-09 12:29:21+01
home
45
null

2020-06-23 14:03:44+01
office
70
50

2020-06-23 14:03:45+01
office
70
50

2020-07-09 12:09:40+01
office
70
18

2020-07-09 12:09:41+01
office
65
null

2020-07-09 12:10:36+01
office
65
45

2020-07-09 12:10:37+01
office
65
37

2020-07-09 12:11:15+01
office
65
32

2020-07-09 12:11:16+01
office
65
32

2020-07-09 12:29:20+01
office
45
null

2020-07-09 12:29:21+01
office
45
26

2020-06-23 14:03:46+01
office2
68
36

2020-06-23 14:03:48+01
office2
68
36

2020-06-23 14:03:51+01
office2
68
null

2020-06-23 14:03:53+01
office2
72
36

2020-06-23 14:03:54+01
office2
72
36

2020-06-23 14:03:54+01
office2
72
36

2020-07-09 12:09:40+01
office2
60
null

2020-07-09 12:09:41+01
office2
60
32

2020-07-09 12:09:41+01
office2
60
32

2020-07-09 12:09:42+01
office2
72
null

2020-07-09 12:09:43+01
office2
68
null

2020-07-09 12:09:44+01
office2
60
32

2020-07-09 12:10:38+01
office2
60
36

2020-07-09 12:10:40+01
office2
60
34

2020-07-09 12:10:42+01
office2
60
34

2020-07-09 12:10:43+01
office2
56
26

2020-07-09 12:10:44+01
office2
56
27

2020-07-09 12:10:45+01
office2
56
34

2020-07-09 12:11:18+01
office2
56
32

2020-07-09 12:11:20+01
office2
56
32

2020-07-09 12:11:23+01
office2
45
32

2020-07-09 12:11:24+01
office2
45
32

2020-07-09 12:11:26+01
office2
45
32

2020-07-09 12:11:26+01
office2
45
32

2020-07-09 12:29:23+01
office2
45
null

2020-07-09 12:29:26+01
office2
63
27

2020-07-09 12:29:27+01
office2
45
null

2020-07-09 12:29:28+01
office2
45
null

2020-07-09 12:29:28+01
office2
45
null

2020-07-09 12:29:30+01
office2
45
null

time	location	temperature	humidity	first_value	first_value
2020-06-23 14:03:44+01	home	68	36	68	36
2020-06-23 14:03:45+01	home	68	36	68	36
2020-07-09 12:09:40+01	home	60	null	60	36
2020-07-09 12:09:41+01	home	60	32	60	32
2020-07-09 12:10:36+01	home	null	34	60	34
2020-07-09 12:10:37+01	home	null	34	60	34
2020-07-09 12:11:15+01	home	43	27	43	27
2020-07-09 12:11:16+01	home	null	32	43	32
2020-07-09 12:29:20+01	home	45	null	45	32
2020-07-09 12:29:21+01	home	45	null	45	32
2020-06-23 14:03:44+01	office	70	50	70	50
2020-06-23 14:03:45+01	office	70	50	70	50
2020-07-09 12:09:40+01	office	null	18	70	18
2020-07-09 12:09:41+01	office	65	null	65	18
2020-07-09 12:10:36+01	office	null	45	65	45
2020-07-09 12:10:37+01	office	null	37	65	37
2020-07-09 12:11:15+01	office	null	32	65	32
2020-07-09 12:11:16+01	office	null	32	65	32
2020-07-09 12:29:20+01	office	45	null	45	32
2020-07-09 12:29:21+01	office	null	26	45	26
2020-06-23 14:03:46+01	office2	68	36	68	36
2020-06-23 14:03:48+01	office2	null	36	68	36
2020-06-23 14:03:51+01	office2	68	null	68	36
2020-06-23 14:03:53+01	office2	72	36	72	36
2020-06-23 14:03:54+01	office2	null	36	72	36
2020-06-23 14:03:54+01	office2	null	36	72	36
2020-07-09 12:09:40+01	office2	60	null	60	36
2020-07-09 12:09:41+01	office2	null	32	60	32
2020-07-09 12:09:41+01	office2	null	32	60	32
2020-07-09 12:09:42+01	office2	72	null	72	32
2020-07-09 12:09:43+01	office2	68	null	68	32
2020-07-09 12:09:44+01	office2	60	32	60	32
2020-07-09 12:10:38+01	office2	null	36	60	36
2020-07-09 12:10:40+01	office2	null	34	60	34
2020-07-09 12:10:42+01	office2	null	34	60	34
2020-07-09 12:10:43+01	office2	56	26	56	26
2020-07-09 12:10:44+01	office2	null	27	56	27
2020-07-09 12:10:45+01	office2	null	34	56	34
2020-07-09 12:11:18+01	office2	null	32	56	32
2020-07-09 12:11:20+01	office2	null	32	56	32
2020-07-09 12:11:23+01	office2	45	32	45	32
2020-07-09 12:11:24+01	office2	null	32	45	32
2020-07-09 12:11:26+01	office2	null	32	45	32
2020-07-09 12:11:26+01	office2	null	32	45	32
2020-07-09 12:29:23+01	office2	45	null	45	32
2020-07-09 12:29:26+01	office2	63	27	63	27
2020-07-09 12:29:27+01	office2	45	null	45	27
2020-07-09 12:29:28+01	office2	45	null	45	27
2020-07-09 12:29:28+01	office2	45	null	45	27
2020-07-09 12:29:30+01	office2	45	null	45	27