Postgres 11

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(
   test_id serial PRIMARY KEY
 , date1   date NOT NULL
 , date2   date NOT NULL
);

INSERT INTO test(date1, date2)
VALUES
   ('2012-03-20', '2012-01-05')  -- 2012-02 lies in between
 , ('2012-01-20', '2012-03-05')  -- 2012-02 (reversed)
 , ('2012-05-06', '2012-05-05')  -- nothing
 , ('2012-05-01', '2012-06-30')  -- still nothing
 , ('2012-08-20', '2012-11-05')  -- 2012-09 - 2012-10
 , ('2012-11-20', '2013-03-05')  -- 2012-12 - 2013-02
;
6 rows affected

-- Postgres 9.3 or later:
SELECT to_char(mon, 'YYYY') AS year
     , to_char(mon, 'MM')   AS month
     , count(*) AS ct
FROM  (
   SELECT date_trunc('month',    least(date1, date2)::timestamp) + interval '1 month' AS d1
        , date_trunc('month', greatest(date1, date2)::timestamp) - interval '1 month' AS d2
   FROM   test
   ) sub1, generate_series(d1, d2, interval '1 month') mon
WHERE  d2 >= d1 -- exclude ranges without gap right away
GROUP  BY mon
ORDER  BY mon;


year
month
ct


2012
02
2

2012
09
1

2012
10
1

2012
12
1

2013
01
1

2013
02
1


-- Postgres 9.2 or older
SELECT to_char(mon, 'YYYY') AS year
     , to_char(mon, 'MM')   AS month
     , count(*) AS ct
FROM  (
   SELECT generate_series(d1, d2, interval '1 month') AS mon
   FROM  (
      SELECT date_trunc('month',    least(date1, date2)::timestamp) + interval '1 month' AS d1
           , date_trunc('month', greatest(date1, date2)::timestamp) - interval '1 month' AS d2
      FROM   test
      ) sub1
   WHERE  d2 >= d1 -- exclude ranges without gap right away
   ) sub2
GROUP  BY mon
ORDER  BY mon;


year
month
ct


2012
02
2

2012
09
1

2012
10
1

2012
12
1

2013
01
1

2013
02
1