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,
  b_date TIMESTAMP(0) WITH TIME ZONE,
  CONSTRAINT action_ck CHECK (action IN ('started', 'suspended'))
  -- CONSTRAINT action_start_uq UNIQUE (action, a_date)
);




CREATE UNIQUE INDEX action_started_a_date_uq ON tab (action, a_date)
  WHERE action = 'started';
CREATE UNIQUE INDEX action_suspended_a_date_uq ON tab (action, a_date)
  WHERE action = 'suspended';
INSERT INTO tab
VALUES
(1  , 'started'   , '2020-08-18 13:51:02','2020-08-18 13:51:02'),
(1  , 'suspended' , '2020-08-18 13:51:04','2020-08-18 13:51:04'),
(2  , 'started'   , '2020-08-18 13:52:14','2020-08-18 13:52:14'),
(2  , 'suspended' , '2020-08-18 13:52:17','2020-08-18 13:52:17'),
(3  , 'started'   , '2020-08-18 13:52:21','2020-08-18 13:52:21'),
(3  , 'suspended' , '2020-08-18 13:52:24','2020-08-18 13:52:24'),
(1  , 'started'   , '2020-08-18 13:57:21','2020-08-18 13:57:21'),
(1  , 'suspended' , '2020-08-18 13:57:22','2020-08-18 13:57:22'),
(1  , 'started'   , '2020-08-18 15:07:56','2020-08-18 15:07:56'),
(1  , 'suspended' , '2020-08-18 15:08:56','2020-08-18 15:08:56'),
(1  , 'started'   , '2020-08-18 15:09:11','2020-08-18 15:09:11'),
(1  , 'suspended' , '2020-08-18 15:09:11','2020-08-18 15:09:11'),
-- (1  , 'started'   , '2020-08-18 15:09:11','2020-08-18 15:09:11'),
(1  , 'suspended' , '2020-08-18 15:09:13','2020-08-18 15:09:13'),

(1  , 'suspended' , '2020-08-18 15:09:13.4567','2020-08-18 15:09:13.1234');
ERROR:  duplicate key value violates unique constraint "action_suspended_a_date_uq"
DETAIL:  Key (action, a_date)=(suspended, 2020-08-18 15:09:13) already exists.


SELECT * FROM tab;
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;


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
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');
ERROR:  duplicate key value violates unique constraint "action_started_a_date_uq"
DETAIL:  Key (action, a_date)=(started, 2020-08-18 15:09:11) already exists.