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 tab
(
  id INTEGER NOT NULL,
  action VARCHAR (10) NOT NULL,
  a_date TIMESTAMP(0) NOT NULL,
  CONSTRAINT action_ck CHECK (action IN ('started', 'suspended'))
);
INSERT INTO tab
VALUES
(1  , 'started'   , '2020-08-18 13:51:02'),
(1  , 'suspended' , '2020-08-18 13:51:04'),
(2  , 'started'   , '2020-08-18 13:52:14'),
(2  , 'suspended' , '2020-08-18 13:52:17'),
(3  , 'started'   , '2020-08-18 13:52:21'),
(3  , 'suspended' , '2020-08-18 13:52:24'),
(1  , 'started'   , '2020-08-18 13:57:21'),
(1  , 'suspended' , '2020-08-18 13:57:22'),
(1  , 'started'   , '2020-08-18 15:07:56'),
(1  , 'suspended' , '2020-08-18 15:08:56'),
(1  , 'started'   , '2020-08-18 15:09:11'),
(1  , 'suspended' , '2020-08-18 15:09:11'),
(1  , 'started'   , '2020-08-18 15:09:11'),
(1  , 'suspended' , '2020-08-18 15:09:13');
14 rows affected

SELECT * FROM data;
ERROR:  relation "data" does not exist
LINE 1: SELECT * FROM data;
                      ^


WITH t AS (
    SELECT id, action, a_date
         , min(a_date) OVER (PARTITION BY 
                           round(extract(epoch FROM a_date)/240)) min_date
         , max(a_date) OVER (PARTITION BY 
                           round(extract(epoch FROM a_date)/240)) max_date
    FROM tab
)
SELECT id, action, a_date, EXTRACT(EPOCH FROM a_date)
FROM t
WHERE a_date = min_date OR a_date = max_date;




id
action
a_date
date_part


1
started
2020-08-18 13:51:02
1597758662

3
suspended
2020-08-18 13:52:24
1597758744

1
started
2020-08-18 13:57:21
1597759041

1
suspended
2020-08-18 13:57:22
1597759042

1
started
2020-08-18 15:07:56
1597763276

1
suspended
2020-08-18 15:09:13
1597763353


WITH t AS (
    SELECT id, action, a_date
         , min(a_date) OVER (PARTITION BY 
                           round(extract(epoch FROM a_date)/240)) min_date
         , max(a_date) OVER (PARTITION BY 
                           round(extract(epoch FROM a_date)/240)) max_date
    FROM tab
)
SELECT id, action, a_date, min_date, max_date
FROM t


id
action
a_date
min_date
max_date


1
started
2020-08-18 13:51:02
2020-08-18 13:51:02
2020-08-18 13:52:24

1
suspended
2020-08-18 13:51:04
2020-08-18 13:51:02
2020-08-18 13:52:24

2
started
2020-08-18 13:52:14
2020-08-18 13:51:02
2020-08-18 13:52:24

2
suspended
2020-08-18 13:52:17
2020-08-18 13:51:02
2020-08-18 13:52:24

3
started
2020-08-18 13:52:21
2020-08-18 13:51:02
2020-08-18 13:52:24

3
suspended
2020-08-18 13:52:24
2020-08-18 13:51:02
2020-08-18 13:52:24

1
started
2020-08-18 13:57:21
2020-08-18 13:57:21
2020-08-18 13:57:22

1
suspended
2020-08-18 13:57:22
2020-08-18 13:57:21
2020-08-18 13:57:22

1
started
2020-08-18 15:07:56
2020-08-18 15:07:56
2020-08-18 15:09:13

1
suspended
2020-08-18 15:08:56
2020-08-18 15:07:56
2020-08-18 15:09:13

1
started
2020-08-18 15:09:11
2020-08-18 15:07:56
2020-08-18 15:09:13

1
suspended
2020-08-18 15:09:11
2020-08-18 15:07:56
2020-08-18 15:09:13

1
started
2020-08-18 15:09:11
2020-08-18 15:07:56
2020-08-18 15:09:13

1
suspended
2020-08-18 15:09:13
2020-08-18 15:07:56
2020-08-18 15:09:13


1  | started   | 2020-08-18 13:51:02
1  | suspended | 2020-08-18 13:52:24
1  | started   | 2020-08-18 13:57:21
1  | suspended | 2020-08-18 13:57:22
1  | started   | 2020-08-18 15:07:56
1  | suspended | 2020-08-18 15:09:13
ERROR:  syntax error at or near "1"
LINE 1: 1  | started   | 2020-08-18 13:51:02
        ^

id	action	a_date	date_part
1	started	2020-08-18 13:51:02	1597758662
3	suspended	2020-08-18 13:52:24	1597758744
1	started	2020-08-18 13:57:21	1597759041
1	suspended	2020-08-18 13:57:22	1597759042
1	started	2020-08-18 15:07:56	1597763276
1	suspended	2020-08-18 15:09:13	1597763353