Postgres 13

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 example (
  time          TIMESTAMP,
  num_issues    INT,
  actor_login   VARCHAR(32)
)

CREATE TABLE


INSERT INTO
  example
VALUES
  ('2015-11-10 01:00:00+01',          2, 'nifl'),
  ('2015-12-10 01:00:00+01',          1, 'anandtrex'),
  ('2016-01-09 01:00:00+01',          1, 'isaacrg'),
  ('2016-02-08 01:00:00+01',          1, 'timbarclay'),
  ('2016-06-07 02:00:00+02',          1, 'kcalmes'),
  ('2016-07-07 02:00:00+02',          1, 'cassiozen'),
  ('2016-08-06 02:00:00+02',         13, 'phae'),
  ('2016-09-05 02:00:00+02',          2, 'phae'),
  ('2016-10-05 02:00:00+02',         13, 'cassiozen'),
  ('2016-11-04 01:00:00+01',          6, 'cassiozen'),
  ('2016-12-04 01:00:00+01',          4, 'cassiozen'),
  ('2017-01-03 01:00:00+01',          5, 'cassiozen'),
  ('2017-02-02 01:00:00+01',          8, 'cassandraoid'),
  ('2017-03-04 01:00:00+01',         16, 'erquhart'),
  ('2017-04-03 02:00:00+02',          3, 'erquhart'),
  ('2017-05-03 02:00:00+02',          9, 'erquhart'),
  ('2017-06-02 02:00:00+02',          5, 'erquhart'),
  ('2017-07-02 02:00:00+02',          2, 'greatwarlive'),
  ('2017-08-01 02:00:00+02',          8, 'tech4him1'),
  ('2017-08-31 02:00:00+02',          7, 'tech4him1'),
  ('2017-09-30 02:00:00+02',         17, 'erquhart'),
  ('2017-10-30 01:00:00+01',          7, 'erquhart'),
  ('2017-11-29 01:00:00+01',         12, 'erquhart'),
  ('2017-12-29 01:00:00+01',          8, 'tech4him1'),
  ('2018-01-28 01:00:00+01',          6, 'ragasirtahk')

INSERT 0 25


WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (                         ORDER BY time)   AS rn,
    ROW_NUMBER() OVER (PARTITION BY actor_login ORDER BY time)   AS rn_actor
  FROM
    example
)
SELECT
  *,
  rn - rn_actor,
  MIN(rn) OVER (PARTITION BY actor_login, rn - rn_actor)  AS group_id
FROM
  sorted
ORDER BY
  rn


time
num_issues
actor_login
rn
rn_actor
?column?
group_id


2015-11-10 01:00:00
2
nifl
1
1
0
1

2015-12-10 01:00:00
1
anandtrex
2
1
1
2

2016-01-09 01:00:00
1
isaacrg
3
1
2
3

2016-02-08 01:00:00
1
timbarclay
4
1
3
4

2016-06-07 02:00:00
1
kcalmes
5
1
4
5

2016-07-07 02:00:00
1
cassiozen
6
1
5
6

2016-08-06 02:00:00
13
phae
7
1
6
7

2016-09-05 02:00:00
2
phae
8
2
6
7

2016-10-05 02:00:00
13
cassiozen
9
2
7
9

2016-11-04 01:00:00
6
cassiozen
10
3
7
9

2016-12-04 01:00:00
4
cassiozen
11
4
7
9

2017-01-03 01:00:00
5
cassiozen
12
5
7
9

2017-02-02 01:00:00
8
cassandraoid
13
1
12
13

2017-03-04 01:00:00
16
erquhart
14
1
13
14

2017-04-03 02:00:00
3
erquhart
15
2
13
14

2017-05-03 02:00:00
9
erquhart
16
3
13
14

2017-06-02 02:00:00
5
erquhart
17
4
13
14

2017-07-02 02:00:00
2
greatwarlive
18
1
17
18

2017-08-01 02:00:00
8
tech4him1
19
1
18
19

2017-08-31 02:00:00
7
tech4him1
20
2
18
19

2017-09-30 02:00:00
17
erquhart
21
5
16
21

2017-10-30 01:00:00
7
erquhart
22
6
16
21

2017-11-29 01:00:00
12
erquhart
23
7
16
21

2017-12-29 01:00:00
8
tech4him1
24
3
21
24

2018-01-28 01:00:00
6
ragasirtahk
25
1
24
25



SELECT 25

time	num_issues	actor_login	rn	rn_actor	?column?	group_id
2015-11-10 01:00:00	2	nifl	1	1	0	1
2015-12-10 01:00:00	1	anandtrex	2	1	1	2
2016-01-09 01:00:00	1	isaacrg	3	1	2	3
2016-02-08 01:00:00	1	timbarclay	4	1	3	4
2016-06-07 02:00:00	1	kcalmes	5	1	4	5
2016-07-07 02:00:00	1	cassiozen	6	1	5	6
2016-08-06 02:00:00	13	phae	7	1	6	7
2016-09-05 02:00:00	2	phae	8	2	6	7
2016-10-05 02:00:00	13	cassiozen	9	2	7	9
2016-11-04 01:00:00	6	cassiozen	10	3	7	9
2016-12-04 01:00:00	4	cassiozen	11	4	7	9
2017-01-03 01:00:00	5	cassiozen	12	5	7	9
2017-02-02 01:00:00	8	cassandraoid	13	1	12	13
2017-03-04 01:00:00	16	erquhart	14	1	13	14
2017-04-03 02:00:00	3	erquhart	15	2	13	14
2017-05-03 02:00:00	9	erquhart	16	3	13	14
2017-06-02 02:00:00	5	erquhart	17	4	13	14
2017-07-02 02:00:00	2	greatwarlive	18	1	17	18
2017-08-01 02:00:00	8	tech4him1	19	1	18	19
2017-08-31 02:00:00	7	tech4him1	20	2	18	19
2017-09-30 02:00:00	17	erquhart	21	5	16	21
2017-10-30 01:00:00	7	erquhart	22	6	16	21
2017-11-29 01:00:00	12	erquhart	23	7	16	21
2017-12-29 01:00:00	8	tech4him1	24	3	21	24
2018-01-28 01:00:00	6	ragasirtahk	25	1	24	25