Postgres 10

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 test_data (
    id serial primary key,
    key text,
    timestamp timestamp with time zone    
);
INSERT INTO test_data
    (key, timestamp)
VALUES
    ('Source_A', '2018-03-15 01:07:06.603029+00'),
    ('Source_B', '2018-03-15 10:00:01.603029+00'),
    ('Source_A', '2018-03-15 11:05:06.603029+00'),
    ('Source_B', '2018-03-15 15:09:06.603029+00'),
    ('Source_B', '2018-03-15 16:09:06.603029+00');

CREATE TABLE



INSERT 0 5


SELECT 
TO_TIMESTAMP(FLOOR((EXTRACT('epoch' FROM timestamp) / 3600 )) * 3600),
date_trunc('hour', timestamp)
from test_data


to_timestamp
date_trunc


2018-03-15 01:00:00+00
2018-03-15 01:00:00+00

2018-03-15 10:00:00+00
2018-03-15 10:00:00+00

2018-03-15 11:00:00+00
2018-03-15 11:00:00+00

2018-03-15 15:00:00+00
2018-03-15 15:00:00+00

2018-03-15 16:00:00+00
2018-03-15 16:00:00+00



SELECT 5


SELECT 
COUNT(hours)-1 AS missing_hours, 
'Source_A' AS key
FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') AS hours
  WHERE hours NOT IN 
  ( SELECT TO_TIMESTAMP(FLOOR((EXTRACT('epoch' FROM timestamp) / 3600 )) * 3600) AS time_bit 
   FROM test_data
   WHERE key = 'Source_A'
   GROUP BY time_bit)


missing_hours
key


22
Source_A



SELECT 1


WITH period as (
  SELECT COUNT(*) as total_hours 
  FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') gs
),
key_counts as (
  SELECT key, COUNT(*) as hours
  FROM (
    SELECT distinct key, date_trunc('hour', timestamp)
    FROM test_data
    --apply period limit here
  ) kq
  GROUP BY KEY
)

SELECT key, total_hours-hours as missing_hours 
FROM 
  period,
  key_counts
  


key
missing_hours


Source_A
23

Source_B
22



SELECT 2

to_timestamp	date_trunc
2018-03-15 01:00:00+00	2018-03-15 01:00:00+00
2018-03-15 10:00:00+00	2018-03-15 10:00:00+00
2018-03-15 11:00:00+00	2018-03-15 11:00:00+00
2018-03-15 15:00:00+00	2018-03-15 15:00:00+00
2018-03-15 16:00:00+00	2018-03-15 16:00:00+00