Postgres 16

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 metering_ranges (
  metering_point_id text NOT NULL
, device_id text NOT NULL
, start_at timestamp NOT NULL  -- !
, end_at timestamp NOT NULL    -- !
);

INSERT INTO metering_ranges(metering_point_id, device_id,  start_at, end_at) VALUES 
  ('meter1', 'device1', '2020-01-02 10:30', '2025-01-02 14:00')  -- max case
, ('meter2', 'device1', '2020-01-02 10:30', '2020-01-02 11:30')  -- min case
, ('meter3', 'device1', '2020-01-02 10:30', '2020-01-03 11:30')  -- cross midnight
, ('meter4', 'device1', '2020-01-02 00:00', '2020-01-03 00:00')  -- full day
, ('meter5', 'device1', '2020-01-02 00:00', '2020-01-07 00:00')  -- full days
, ('meter6', 'device1', '2020-01-02 00:00', '2020-01-03 01:00')  -- start 0:0
, ('meter7', 'device1', '2020-01-02 10:00', '2020-01-04 00:00')  -- end 0:0
, ('meter8', 'device1', '2020-01-02 00:00', '2020-01-02 01:00')  -- start 0:0, same day
, ('meter9', 'device1', '2020-01-02 10:00', '2020-01-03 00:00')  -- end 0:0, same day
;

CREATE TABLE



INSERT 0 9


-- org by Pete
with ranges_with_whole_days as (
    SELECT
        metering_point_id,
        device_id,
        start_at,
        date_trunc('day', start_at) + interval '1 d' as start_at_next_whole_day,
        date_trunc('day', end_at) as end_at_whole_day,
        end_at
    FROM
        metering_ranges
   )
, ranges as (
    SELECT
        metering_point_id,
        device_id,
        start_at,
        CASE
            WHEN start_at_next_whole_day <= end_at_whole_day THEN start_at_next_whole_day ELSE NULL
        END as start_at_next_day,
        CASE 
            WHEN end_at_whole_day >= start_at_next_whole_day THEN end_at_whole_day ELSE NULL
        END as end_at_prev_day,
        end_at
    FROM
        ranges_with_whole_days
   )
, ranges_bucketed AS (
    -- get hours before whole day
    SELECT metering_point_id, device_id, start_at, start_at_next_day as end_at
    FROM ranges m
    WHERE start_at_next_day IS NOT NULL
    
    UNION
    
    -- get whole day period
    SELECT metering_point_id, device_id, start_at_next_day as start_at, end_at_prev_day as end_at
    FROM  ranges m
    WHERE start_at_next_day IS NOT NULL AND end_at_prev_day IS NOT NULL AND start_at_next_day != end_at_prev_day
    
    UNION
    
    -- get hours after whole day
    SELECT metering_point_id, device_id, end_at_prev_day as start_at, end_at
    FROM ranges m
    WHERE end_at_prev_day IS NOT NULL
    
    UNION
    
    -- get existing record if it fits within a day
    SELECT  metering_point_id,  device_id, start_at, end_at
    FROM ranges m
    WHERE start_at_next_day IS NULL AND end_at_prev_day IS NULL
   ) 
SELECT *
FROM ranges_bucketed
ORDER BY metering_point_id, device_id, start_at


metering_point_id
device_id
start_at
end_at


meter1
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter1
device1
2020-01-03 00:00:00
2025-01-02 00:00:00

meter1
device1
2025-01-02 00:00:00
2025-01-02 14:00:00

meter2
device1
2020-01-02 10:30:00
2020-01-02 11:30:00

meter3
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter3
device1
2020-01-03 00:00:00
2020-01-03 11:30:00

meter4
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter4
device1
2020-01-03 00:00:00
2020-01-03 00:00:00

meter5
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter5
device1
2020-01-03 00:00:00
2020-01-07 00:00:00

meter5
device1
2020-01-07 00:00:00
2020-01-07 00:00:00

meter6
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter6
device1
2020-01-03 00:00:00
2020-01-03 01:00:00

meter7
device1
2020-01-02 10:00:00
2020-01-03 00:00:00

meter7
device1
2020-01-03 00:00:00
2020-01-04 00:00:00

meter7
device1
2020-01-04 00:00:00
2020-01-04 00:00:00

meter8
device1
2020-01-02 00:00:00
2020-01-02 01:00:00

meter9
device1
2020-01-02 10:00:00
2020-01-03 00:00:00

meter9
device1
2020-01-03 00:00:00
2020-01-03 00:00:00



SELECT 19


-- 1. split all ranges at midnight, string full days together
-- also splits meter3
WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day < start_day

-- split core days
UNION ALL
SELECT metering_point_id, device_id, start_day, end_day
FROM   cte
WHERE  end_day > start_day
-- add leading range
UNION ALL
SELECT metering_point_id, device_id, start_at, start_day
FROM   cte
WHERE  end_day >= start_day
AND    start_at::time > '0:0'
-- add trailing range
UNION ALL
SELECT metering_point_id, device_id, end_day, end_at
FROM   cte
WHERE  end_day >= start_day
AND    end_at::time > '0:0'
ORDER  BY 1,2,3;


metering_point_id
device_id
start_at
end_at


meter1
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter1
device1
2020-01-03 00:00:00
2025-01-02 00:00:00

meter1
device1
2025-01-02 00:00:00
2025-01-02 14:00:00

meter2
device1
2020-01-02 10:30:00
2020-01-02 11:30:00

meter3
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter3
device1
2020-01-03 00:00:00
2020-01-03 11:30:00

meter4
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter5
device1
2020-01-02 00:00:00
2020-01-07 00:00:00

meter6
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter6
device1
2020-01-03 00:00:00
2020-01-03 01:00:00

meter7
device1
2020-01-02 10:00:00
2020-01-03 00:00:00

meter7
device1
2020-01-03 00:00:00
2020-01-04 00:00:00

meter8
device1
2020-01-02 00:00:00
2020-01-02 01:00:00

meter9
device1
2020-01-02 10:00:00
2020-01-03 00:00:00



SELECT 14


-- 2. Only split of stretches of full days
-- does not split meter3
WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day <= start_day

-- split core days
UNION ALL
SELECT metering_point_id, device_id, start_day, end_day
FROM   cte
WHERE  end_day > start_day
-- add leading range
UNION ALL
SELECT metering_point_id, device_id, start_at, start_day
FROM   cte
WHERE  end_day > start_day
AND    start_at::time <> '0:0'
-- add trailing range
UNION ALL
SELECT metering_point_id, device_id, end_day, end_at
FROM   cte
WHERE  end_day > start_day
AND    end_at::time <> '0:0'
ORDER  BY 1,2,3;


metering_point_id
device_id
start_at
end_at


meter1
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter1
device1
2020-01-03 00:00:00
2025-01-02 00:00:00

meter1
device1
2025-01-02 00:00:00
2025-01-02 14:00:00

meter2
device1
2020-01-02 10:30:00
2020-01-02 11:30:00

meter3
device1
2020-01-02 10:30:00
2020-01-03 11:30:00

meter4
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter5
device1
2020-01-02 00:00:00
2020-01-07 00:00:00

meter6
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter6
device1
2020-01-03 00:00:00
2020-01-03 01:00:00

meter7
device1
2020-01-02 10:00:00
2020-01-03 00:00:00

meter7
device1
2020-01-03 00:00:00
2020-01-04 00:00:00

meter8
device1
2020-01-02 00:00:00
2020-01-02 01:00:00

meter9
device1
2020-01-02 10:00:00
2020-01-03 00:00:00



SELECT 13


-- variant for 2. - with multiranges (Postgres 14+)
WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day <= start_day

-- nested full days to split
UNION ALL
SELECT metering_point_id, device_id, x.*
FROM   cte
CROSS  JOIN LATERAL (
   VALUES (start_day, end_day)
   UNION ALL
   SELECT lower(rest), upper(rest)
   FROM   unnest(tsrange(start_at, end_at)::tsmultirange
               - tsrange(start_day, end_day)::tsmultirange) rest
   ) x
WHERE  end_day > start_day
ORDER  BY 1,2,3;


metering_point_id
device_id
start_at
end_at


meter1
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter1
device1
2020-01-03 00:00:00
2025-01-02 00:00:00

meter1
device1
2025-01-02 00:00:00
2025-01-02 14:00:00

meter2
device1
2020-01-02 10:30:00
2020-01-02 11:30:00

meter3
device1
2020-01-02 10:30:00
2020-01-03 11:30:00

meter4
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter5
device1
2020-01-02 00:00:00
2020-01-07 00:00:00

meter6
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter6
device1
2020-01-03 00:00:00
2020-01-03 01:00:00

meter7
device1
2020-01-02 10:00:00
2020-01-03 00:00:00

meter7
device1
2020-01-03 00:00:00
2020-01-04 00:00:00

meter8
device1
2020-01-02 00:00:00
2020-01-02 01:00:00

meter9
device1
2020-01-02 10:00:00
2020-01-03 00:00:00



SELECT 13


-- variant for 2. - even shorter (not necessarily faster)
SELECT c.metering_point_id, c.device_id
     , COALESCE(x.start_at, c.start_at) AS start_at
     , COALESCE(x.end_at, c.end_at) AS end_at
FROM  (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   ) c
LEFT   JOIN  LATERAL (
   VALUES (start_day, end_day)
   UNION ALL
   SELECT lower(rest), upper(rest)
   FROM   unnest(tsrange(start_at, end_at)::tsmultirange
               - tsrange(start_day, end_day)::tsmultirange) rest
   WHERE  c.end_day > c.start_day
   ) x(start_at, end_at) ON c.end_day > c.start_day
ORDER  BY 1,2,3;


metering_point_id
device_id
start_at
end_at


meter1
device1
2020-01-02 10:30:00
2020-01-03 00:00:00

meter1
device1
2020-01-03 00:00:00
2025-01-02 00:00:00

meter1
device1
2025-01-02 00:00:00
2025-01-02 14:00:00

meter2
device1
2020-01-02 10:30:00
2020-01-02 11:30:00

meter3
device1
2020-01-02 10:30:00
2020-01-03 11:30:00

meter4
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter5
device1
2020-01-02 00:00:00
2020-01-07 00:00:00

meter6
device1
2020-01-02 00:00:00
2020-01-03 00:00:00

meter6
device1
2020-01-03 00:00:00
2020-01-03 01:00:00

meter7
device1
2020-01-02 10:00:00
2020-01-03 00:00:00

meter7
device1
2020-01-03 00:00:00
2020-01-04 00:00:00

meter8
device1
2020-01-02 00:00:00
2020-01-02 01:00:00

meter9
device1
2020-01-02 10:00:00
2020-01-03 00:00:00



SELECT 13

metering_point_id	device_id	start_at	end_at
meter1	device1	2020-01-02 10:30:00	2020-01-03 00:00:00
meter1	device1	2020-01-03 00:00:00	2025-01-02 00:00:00
meter1	device1	2025-01-02 00:00:00	2025-01-02 14:00:00
meter2	device1	2020-01-02 10:30:00	2020-01-02 11:30:00
meter3	device1	2020-01-02 10:30:00	2020-01-03 00:00:00
meter3	device1	2020-01-03 00:00:00	2020-01-03 11:30:00
meter4	device1	2020-01-02 00:00:00	2020-01-03 00:00:00
meter4	device1	2020-01-03 00:00:00	2020-01-03 00:00:00
meter5	device1	2020-01-02 00:00:00	2020-01-03 00:00:00
meter5	device1	2020-01-03 00:00:00	2020-01-07 00:00:00
meter5	device1	2020-01-07 00:00:00	2020-01-07 00:00:00
meter6	device1	2020-01-02 00:00:00	2020-01-03 00:00:00
meter6	device1	2020-01-03 00:00:00	2020-01-03 01:00:00
meter7	device1	2020-01-02 10:00:00	2020-01-03 00:00:00
meter7	device1	2020-01-03 00:00:00	2020-01-04 00:00:00
meter7	device1	2020-01-04 00:00:00	2020-01-04 00:00:00
meter8	device1	2020-01-02 00:00:00	2020-01-02 01:00:00
meter9	device1	2020-01-02 10:00:00	2020-01-03 00:00:00
meter9	device1	2020-01-03 00:00:00	2020-01-03 00:00:00