Postgres 13

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 acs_objects (creation_date timestamp);

INSERT INTO acs_objects
SELECT '1970-1-1 0:0'::timestamp + random() * interval '46 years'
FROM   generate_series(1,50) g  -- test with more rows at home, not in the fiddle
;
CREATE INDEX foo ON acs_objects (creation_date);
50 rows affected

-- recursive CTE
WITH RECURSIVE cte AS (
   SELECT date_trunc('year', max(creation_date)) AS y
   FROM   acs_objects

   UNION ALL
   SELECT (SELECT date_trunc('year', max(creation_date))
           FROM   acs_objects
           WHERE  creation_date < cte.y)
   FROM   cte
   WHERE  cte.y IS NOT NULL
   )
SELECT to_char(y, 'YYYY') AS year
FROM   cte
WHERE  cte.y IS NOT NULL;

year

2015

2014

2013

2012

2011

2010

2008

2007

2006

2005

2004

2002

2001

1999

1997

1996

1995

1994

1993

1991

1989

1987

1986

1985

1983

1981

1980

1979

1978

1975

1974

1973

1971

-- generate_series() & EXISTS
SELECT to_char(y, 'YYYY') AS year
FROM  (
   SELECT generate_series(date_trunc('year', min(creation_date))
                        , max(creation_date)
                        , interval  '1 year')
   FROM   acs_objects
   ) t(y)
WHERE  EXISTS (
   SELECT FROM acs_objects 
   WHERE creation_date >= y
   AND   creation_date <  y + interval '1 year'
   );

year

1971

1973

1974

1975

1978

1979

1980

1981

1983

1985

1986

1987

1989

1991

1993

1994

1995

1996

1997

1999

2001

2002

2004

2005

2006

2007

2008

2010

2011

2012

2013

2014

2015