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 mytable (
    ts timestamp
);

INSERT INTO mytable VALUES
 ('2013-09-14 14:19:46'),    
 ('2013-09-14 15:11:48'),    
 ('2013-09-14 16:11:49'),     
 ('2013-09-14 17:13:45'),    
 ('2013-09-22 00:08:38'),    
 ('2013-09-22 00:10:11'),    
 ('2013-09-22 01:11:36'),   
 ('2013-09-22 02:16:11'),  
 ('2013-09-22 03:13:16'),     
 ('2013-09-22 04:05:38'),  
 ('2013-09-22 06:10:11'),     
 ('2013-09-22 07:26:43'),    
 ('2013-09-22 08:17:35'),     
 ('2013-09-22 09:16:08'),    
 ('2013-09-22 10:16:08'),  
 ('2013-10-01 06:15:07'),   
 ('2013-10-01 06:15:12'),   
 ('2013-10-02 07:15:15'), 
 ('2013-10-02 08:05:09');
 
SELECT * FROM mytable
19 rows affected



ts


2013-09-14 14:19:46

2013-09-14 15:11:48

2013-09-14 16:11:49

2013-09-14 17:13:45

2013-09-22 00:08:38

2013-09-22 00:10:11

2013-09-22 01:11:36

2013-09-22 02:16:11

2013-09-22 03:13:16

2013-09-22 04:05:38

2013-09-22 06:10:11

2013-09-22 07:26:43

2013-09-22 08:17:35

2013-09-22 09:16:08

2013-09-22 10:16:08

2013-10-01 06:15:07

2013-10-01 06:15:12

2013-10-02 07:15:15

2013-10-02 08:05:09


SELECT
    *,
    round(extract(epoch from lead(ts) OVER (ORDER BY ts) - ts) / 60 / 60) as diff
FROM mytable


ts
diff


2013-09-14 14:19:46
1

2013-09-14 15:11:48
1

2013-09-14 16:11:49
1

2013-09-14 17:13:45
175

2013-09-22 00:08:38
0

2013-09-22 00:10:11
1

2013-09-22 01:11:36
1

2013-09-22 02:16:11
1

2013-09-22 03:13:16
1

2013-09-22 04:05:38
2

2013-09-22 06:10:11
1

2013-09-22 07:26:43
1

2013-09-22 08:17:35
1

2013-09-22 09:16:08
1

2013-09-22 10:16:08
212

2013-10-01 06:15:07
0

2013-10-01 06:15:12
25

2013-10-02 07:15:15
1

2013-10-02 08:05:09
null


SELECT
    *,
    SUM(
        CASE 
            WHEN diff >= 5 THEN 1
            ELSE 0
        END
    ) OVER (ORDER BY ts)
FROM (
    SELECT
        *,
        round(extract(epoch from lead(ts) OVER (ORDER BY ts) - ts) / 60 / 60) as diff
    FROM mytable
) s


ts
diff
sum


2013-09-14 14:19:46
1
0

2013-09-14 15:11:48
1
0

2013-09-14 16:11:49
1
0

2013-09-14 17:13:45
175
1

2013-09-22 00:08:38
0
1

2013-09-22 00:10:11
1
1

2013-09-22 01:11:36
1
1

2013-09-22 02:16:11
1
1

2013-09-22 03:13:16
1
1

2013-09-22 04:05:38
2
1

2013-09-22 06:10:11
1
1

2013-09-22 07:26:43
1
1

2013-09-22 08:17:35
1
1

2013-09-22 09:16:08
1
1

2013-09-22 10:16:08
212
2

2013-10-01 06:15:07
0
2

2013-10-01 06:15:12
25
3

2013-10-02 07:15:15
1
3

2013-10-02 08:05:09
null
3

ts	diff
2013-09-14 14:19:46	1
2013-09-14 15:11:48	1
2013-09-14 16:11:49	1
2013-09-14 17:13:45	175
2013-09-22 00:08:38	0
2013-09-22 00:10:11	1
2013-09-22 01:11:36	1
2013-09-22 02:16:11	1
2013-09-22 03:13:16	1
2013-09-22 04:05:38	2
2013-09-22 06:10:11	1
2013-09-22 07:26:43	1
2013-09-22 08:17:35	1
2013-09-22 09:16:08	1
2013-09-22 10:16:08	212
2013-10-01 06:15:07	0
2013-10-01 06:15:12	25
2013-10-02 07:15:15	1
2013-10-02 08:05:09	null