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 devices(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE);
CREATE TABLE results(id INTEGER PRIMARY KEY, test_name VARCHAR, results JSON, state VARCHAR);
CREATE TABLE results_for_device(results_id INTEGER, device_id INTEGER, PRIMARY KEY(results_id, device_id));
INSERT INTO devices(id, name) VALUES
	(1, 'Dev1'),
	(2, 'Dev2');
2 rows affected

INSERT INTO results(id, test_name, results, state) VALUES

	-- Results thread for 'test1' and 'Dev1'
	(1, 'test1', '{}', 'CURRENT_BLUEPRINT'),
	(2, 'test1', '{}', 'MATCHING_BLUEPRINT'),
	(3, 'test1', '{}', 'MATCHING_BLUEPRINT'),
	(4, 'test1', '{}', 'NOT_MATCHING_BLUEPRINT'),
                                                                                   
	-- Results thread for 'test1' and 'Dev2'
	(5, 'test1', '{}', 'OLD_BLUEPRINT'),
	(6, 'test1', '{}', 'NOT_MATCHING_BLUEPRINT'),
	(7, 'test1', '{}', 'CURRENT_BLUEPRINT'),
	(8, 'test1', '{}', 'MATCHING_BLUEPRINT'),
	(9, 'test1', '{}', 'MATCHING_BLUEPRINT'),
                                                                                   
	-- Results thread for 'test2' and 'Dev1'
	(10, 'test2', '{}', 'OLD_BLUEPRINT'),
	(11, 'test2', '{}', 'NOT_MATCHING_BLUEPRINT'),
	(12, 'test2', '{}', 'CURRENT_BLUEPRINT'),
	(13, 'test2', '{}', 'MATCHING_BLUEPRINT'),
	(14, 'test2', '{}', 'MATCHING_BLUEPRINT'),

	-- Results thread for 'test2' and 'Dev2'
	(15, 'test2', '{}', 'OLD_BLUEPRINT'),
	(16, 'test2', '{}', 'CURRENT_BLUEPRINT'),
	(17, 'test2', '{}', 'MATCHING_BLUEPRINT'),
	(18, 'test2', '{}', 'MATCHING_BLUEPRINT'),
	(19, 'test2', '{}', 'NOT_MATCHING_BLUEPRINT');
19 rows affected

INSERT INTO results_for_device(results_id, device_id) VALUES
	(1, 1),
	(2, 1),
	(3, 1),
	(4, 1),
	(5, 2),
	(6, 2),
	(7, 2),
	(8, 2),
	(9, 2),
	(10, 1),
	(11, 1),
	(12, 1),
	(13, 1),
	(14, 1),
	(15, 2),
	(16, 2),
	(17, 2),
	(18, 2),
	(19, 2);
19 rows affected

WITH
  result_threads AS
  (
    SELECT
      rfd.*,
      r.test_name
    FROM
      results_for_device AS rfd
      INNER JOIN results AS r ON rfd.results_id = r.id
    WHERE
      r.state = 'CURRENT_BLUEPRINT' 
  )
SELECT
  rfd.device_id,
  rfd.results_id,
  r.test_name,
  r.results,
  r.state
FROM
  results_for_device AS rfd
  INNER JOIN results AS r ON rfd.results_id = r.id
  INNER JOIN result_threads AS rt
    ON  rfd.device_id = rt.device_id
    AND r.test_name = rt.test_name
    AND r.id >= rt.results_id
;


device_id
results_id
test_name
results
state


1
1
test1
{}
CURRENT_BLUEPRINT

1
2
test1
{}
MATCHING_BLUEPRINT

1
3
test1
{}
MATCHING_BLUEPRINT

1
4
test1
{}
NOT_MATCHING_BLUEPRINT

2
7
test1
{}
CURRENT_BLUEPRINT

2
8
test1
{}
MATCHING_BLUEPRINT

2
9
test1
{}
MATCHING_BLUEPRINT

1
12
test2
{}
CURRENT_BLUEPRINT

1
13
test2
{}
MATCHING_BLUEPRINT

1
14
test2
{}
MATCHING_BLUEPRINT

2
16
test2
{}
CURRENT_BLUEPRINT

2
17
test2
{}
MATCHING_BLUEPRINT

2
18
test2
{}
MATCHING_BLUEPRINT

2
19
test2
{}
NOT_MATCHING_BLUEPRINT


SELECT
  device_id,
  results_id,
  test_name,
  results,
  state
FROM
  (
    SELECT
      rfd.device_id,
      rfd.results_id,
      r.test_name,
      r.results,
      r.state,
      MAX(r.id) FILTER (WHERE r.state = 'CURRENT_BLUEPRINT') OVER w AS this_id,
      MAX(r.id)                                              OVER w AS max_id
    FROM
      results_for_device AS rfd
      INNER JOIN results AS r ON rfd.results_id = r.id
    WINDOW
      w AS (PARTITION BY rfd.device_id, r.test_name)
  ) AS derived
WHERE
  results_id BETWEEN this_id AND max_id
ORDER BY
  results_id ASC
;


device_id
results_id
test_name
results
state


1
1
test1
{}
CURRENT_BLUEPRINT

1
2
test1
{}
MATCHING_BLUEPRINT

1
3
test1
{}
MATCHING_BLUEPRINT

1
4
test1
{}
NOT_MATCHING_BLUEPRINT

2
7
test1
{}
CURRENT_BLUEPRINT

2
8
test1
{}
MATCHING_BLUEPRINT

2
9
test1
{}
MATCHING_BLUEPRINT

1
12
test2
{}
CURRENT_BLUEPRINT

1
13
test2
{}
MATCHING_BLUEPRINT

1
14
test2
{}
MATCHING_BLUEPRINT

2
16
test2
{}
CURRENT_BLUEPRINT

2
17
test2
{}
MATCHING_BLUEPRINT

2
18
test2
{}
MATCHING_BLUEPRINT

2
19
test2
{}
NOT_MATCHING_BLUEPRINT

device_id	results_id	test_name	results	state
1	1	test1	{}	CURRENT_BLUEPRINT
1	2	test1	{}	MATCHING_BLUEPRINT
1	3	test1	{}	MATCHING_BLUEPRINT
1	4	test1	{}	NOT_MATCHING_BLUEPRINT
2	7	test1	{}	CURRENT_BLUEPRINT
2	8	test1	{}	MATCHING_BLUEPRINT
2	9	test1	{}	MATCHING_BLUEPRINT
1	12	test2	{}	CURRENT_BLUEPRINT
1	13	test2	{}	MATCHING_BLUEPRINT
1	14	test2	{}	MATCHING_BLUEPRINT
2	16	test2	{}	CURRENT_BLUEPRINT
2	17	test2	{}	MATCHING_BLUEPRINT
2	18	test2	{}	MATCHING_BLUEPRINT
2	19	test2	{}	NOT_MATCHING_BLUEPRINT