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 tbl (id int, created_at timestamp);
INSERT INTO tbl VALUES
  (1, '2019-01-01 00:00')
, (2, '2019-01-01 10:00')
, (3, '2019-01-01 10:10')
, (4, '2019-01-01 10:20')
, (5, '2019-01-01 10:30')
, (6, '2019-01-01 23:59:59.999999') -- included
, (7, '2019-01-02 00:00') -- out of bound
;
7 rows affected

WITH cte(min_ts, max_ts, buckets) AS (  -- interval and nr of buckets here
   SELECT timestamp '2019-01-01T00:00:00'
        , timestamp '2019-01-02T00:00:00'
        , 2
   )
SELECT b.*
     , min_ts + ((c.max_ts - c.min_ts) / c.buckets) * (bucket-1) As lower_bound
FROM  (
   SELECT width_bucket(extract(epoch FROM t.created_at)
                     , extract(epoch FROM c.min_ts)
                     , extract(epoch FROM c.max_ts)
                     , c.buckets) AS bucket
        , count(*) AS ct
   FROM   tbl t
   JOIN   cte c ON t.created_at >= min_ts  -- incl. lower
               AND t.created_at <  max_ts  -- excl. upper
   GROUP  BY 1
   ORDER  BY 1
   ) b, cte c;


bucket
ct
lower_bound


1
5
2019-01-01 00:00:00

2
1
2019-01-01 12:00:00


WITH cte(min_ts, max_ts, buckets) AS (
   SELECT timestamp '2019-01-01T00:00:00'
        , timestamp '2019-01-02T00:00:00'
        , 5                                 -- 5 instead of 2 !
   )
SELECT b.*
     , min_ts + ((c.max_ts - c.min_ts) / c.buckets) * (bucket-1) As lower_bound
FROM  (
   SELECT width_bucket(extract(epoch FROM t.created_at)
                     , extract(epoch FROM c.min_ts)
                     , extract(epoch FROM c.max_ts)
                     , c.buckets) AS bucket
        , count(*) AS ct
   FROM   tbl t
   JOIN   cte c ON t.created_at >= min_ts
               AND t.created_at <  max_ts
   GROUP  BY 1
   ORDER  BY 1
   ) b, cte c;


bucket
ct
lower_bound


1
1
2019-01-01 00:00:00

3
4
2019-01-01 09:36:00

5
1
2019-01-01 19:12:00

bucket	ct	lower_bound
1	1	2019-01-01 00:00:00
3	4	2019-01-01 09:36:00
5	1	2019-01-01 19:12:00