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 occurrence 
(
  animal text,
  total int,
  from_date date
);
INSERT INTO occurrence 
  (animal, total, from_date) 
VALUES
  ('cat',1,'1800-01-01'),
  ('bird',1,'1846-06-13'),
  ('dog',1,'1865-05-24'),
  ('dog',11,'1869-05-02'),
  ('dog',1,'1869-05-06'),
  ('dog',1,'1869-05-12'),
  ('cat',1,'1870-01-01'),
  ('dog',2,'1872-06-06'),
  ('dog',1,'1876-04-24'),
  ('dog',1,'1882-05-01'),
  ('dog',1,'1883-04-30'),
  ('dog',1,'1884-05-11'),
  ('cat',1,'1884-06-22'),
  ('dog',1,'1885-04-23'),
  ('cat',1,'1885-07-08'),
  ('cat',1,'1885-07-10'),
  ('dog',1,'1890-05-25'),
  ('dog',1,'1894-05-18'),
  ('bird',1,'1894-06-16'),
  ('bird',1,'1894-06-16'),
  ('cat',1,'1895-05-14'),
  ('dog',1,'1896-06-01'),
  ('dog',1,'1900-05-24'),
  ('dog',1,'1903-05-27'),
  ('cat',1,'1905-03-14'),
  ('bird',3,'1905-04-23'),
  ('dog',1,'1905-05-19'),
  ('dog',1,'1905-05-19'),
  ('dog',1,'1905-05-19'),
  ('dog',3,'1905-06-12'),
  ('cat',1,'1905-06-27'),
  ('dog',1,'1905-06-29'),
  ('bird',1,'1906-05-15'),
  ('bird',1,'1906-05-15'),
  ('bird',1,'1906-05-15'),
  ('bird',1,'1907-05-12'),
  ('bird',1,'1907-05-12'),
  ('bird',1,'1907-05-12'),
  ('bird',1,'1909-06-21'),
  ('bird',1,'1909-06-21'),
  ('bird',3,'1910-05-14'),
  ('bird',4,'1910-05-21'),
  ('bird',1,'1910-05-21'),
  ('dog',3,'1910-05-22'),
  ('dog',1,'1910-05-27'),
  ('dog',1,'1910-05-27'),
  ('dog',1,'1910-05-27'),
  ('dog',1,'1912-05-28'),
  ('dog',1,'1912-06-11'),
  ('dog',1,'1913-05-12'),
  ('dog',1,'1913-05-12'),
  ('bird',2,'1913-05-14'),
  ('bird',2,'1913-05-14'),
  ('dog',1,'1914-01-01'),
  ('cat',3,'1914-07-03'),
  ('cat',1,'1914-07-06'),
  ('cat',1,'1916-05-28'),
  ('dog',1,'1916-05-29'),
  ('dog',1,'1916-05-29'),
  ('dog',1,'1916-05-29'),
  ('cat',1,'1916-06-14'),
  ('bird',1,'1916-06-18'),
  ('dog',1,'1917-05-01'),
  ('dog',1,'1917-05-25'),
  ('dog',1,'1918-01-01'),
  ('dog',3,'1918-05-20'),
  ('dog',1,'1919-05-24'),
  ('dog',1,'1919-05-25'),
  ('dog',2,'1919-05-29'),
  ('bird',1,'1919-06-09'),
  ('bird',1,'1919-06-09'),
  ('dog',2,'1920-05-20'),
  ('bird',1,'1920-05-29'),
  ('bird',1,'1920-05-29'),
  ('dog',1,'1921-05-05'),
  ('dog',1,'1921-05-09'),
  ('dog',1,'1922-05-08'),
  ('cat',1,'1922-06-15'),
  ('dog',1,'1922-07-02'),
  ('dog',1,'1923-04-04'),
  ('dog',1,'1923-05-29'),
  ('bird',1,'1923-06-25'),
  ('dog',1,'1924-01-01'),
  ('dog',1,'1924-05-17'),
  ('bird',1,'1925-04-08'),
  ('bird',1,'1925-04-08'),
  ('bird',1,'1926-06-28'),
  ('bird',2,'1927-05-21'),
  ('bird',2,'1927-05-21'),
  ('bird',1,'1927-05-26'),
  ('dog',1,'1928-06-12'),
  ('cat',1,'1931-05-23'),
  ('dog',1,'1932-05-16'),
  ('bird',2,'1932-06-15'),
  ('bird',1,'1932-06-19'),
  ('bird',3,'1932-06-19'),
  ('bird',1,'1933-07-22'),
  ('cat',1,'1934-06-02'),
  ('bird',1,'1934-06-04'),
  ('bird',1,'1934-06-05'),
  ('bird',1,'1934-06-08'),
  ('bird',1,'1934-06-09'),
  ('cat',1,'1934-06-10'),
  ('bird',1,'1934-06-13'),
  ('bird',1,'1937-05-29'),
  ('cat',1,'1937-06-07'),
  ('bird',1,'1938-07-03'),
  ('cat',1,'1938-07-07'),
  ('dog',1,'1939-01-01'),
  ('dog',3,'1939-05-09'),
  ('dog',1,'1939-05-21'),
  ('dog',1,'1939-05-23'),
  ('dog',1,'1939-05-29'),
  ('cat',1,'1939-06-11'),
  ('bird',1,'1939-06-11'),
  ('dog',2,'1940-05-03'),
  ('dog',3,'1940-05-03'),
  ('dog',1,'1941-05-16'),
  ('cat',1,'1942-07-12'),
  ('cat',1,'1942-07-13'),
  ('dog',1,'1943-04-26'),
  ('dog',1,'1943-05-09'),
  ('bird',1,'1943-05-13'),
  ('dog',1,'1944-05-06'),
  ('dog',1,'1944-05-06'),
  ('cat',1,'1945-07-02'),
  ('dog',1,'1945-07-03'),
  ('dog',1,'1946-04-12'),
  ('dog',1,'1946-05-08'),
  ('bird',1,'1946-05-12'),
  ('bird',1,'1946-05-12'),
  ('bird',2,'1946-05-12'),
  ('bird',1,'1946-07-25'),
  ('bird',1,'1947-06-01'),
  ('cat',1,'1947-06-19'),
  ('bird',1,'1949-04-29'),
  ('dog',1,'1949-05-04'),
  ('bird',1,'1949-05-15'),
  ('bird',1,'1949-06-05'),
  ('cat',2,'1949-07-16'),
  ('bird',3,'1950-04-17'),
  ('cat',1,'1950-06-24'),
  ('dog',1,'1951-05-08'),
  ('bird',1,'1956-05-27'),
  ('cat',1,'1957-06-07'),
  ('dog',1,'1958-01-01'),
  ('bird',1,'1958-06-25'),
  ('bird',1,'1958-07-11'),
  ('cat',1,'1958-07-20'),
  ('bird',15,'1960-05-22'),
  ('bird',1,'1960-05-22'),
  ('dog',1,'1960-05-29'),
  ('dog',1,'1960-06-11'),
  ('cat',2,'1960-06-26'),
  ('dog',0,'1960-09-22'),
  ('dog',1,'1961-04-12'),
  ('cat',1,'1961-06-22'),
  ('bird',1,'1961-07-16'),
  ('cat',1,'1962-06-29'),
  ('cat',2,'1962-07-06'),
  ('bird',1,'1963-05-23'),
  ('bird',1,'1963-06-21'),
  ('bird',1,'1963-06-25'),
  ('dog',2,'1963-07-17'),
  ('dog',0,'1963-07-17'),
  ('cat',1,'1964-04-02'),
  ('bird',1,'1964-05-17'),
  ('dog',1,'1964-05-26'),
  ('bird',1,'1964-06-05'),
  ('bird',1,'1964-06-08'),
  ('bird',1,'1964-06-15'),
  ('cat',1,'1964-06-26'),
  ('bird',1,'1964-07-01'),
  ('cat',1,'1964-07-09'),
  ('bird',1,'1965-06-11'),
  ('bird',1,'1965-07-11'),
  ('dog',1,'1966-05-16'),
  ('cat',1,'1966-06-18'),
  ('cat',1,'1966-07-01'),
  ('dog',0,'1966-08-08'),
  ('dog',0,'1966-08-18'),
  ('dog',1,'1967-01-01'),
  ('dog',1,'1967-05-05'),
  ('bird',1,'1967-05-07'),
  ('dog',1,'1967-05-08'),
  ('bird',1,'1967-05-30'),
  ('bird',1,'1967-06-18'),
  ('bird',1,'1967-06-25'),
  ('dog',1,'1967-06-27'),
  ('bird',1,'1967-06-28'),
  ('cat',1,'1967-07-16'),
  ('cat',1,'1967-07-16'),
  ('dog',1,'1968-04-24'),
  ('bird',1,'1968-05-16'),
  ('bird',1,'1968-05-23'),
  ('bird',1,'1968-05-25'),
  ('dog',2,'1968-05-30'),
  ('cat',1,'1968-06-19'),
  ('dog',1,'1969-05-08'),
  ('dog',2,'1969-05-14'),
  ('bird',1,'1969-06-07'),
  ('bird',1,'1969-06-08'),
  ('bird',1,'1969-06-08'),
  ('bird',3,'1969-06-08'),
  ('bird',2,'1969-06-11'),
  ('bird',1,'1969-06-11'),
  ('bird',1,'1969-06-29'),
  ('bird',1,'1969-06-29'),
  ('cat',1,'1969-07-21'),
  ('cat',1,'1969-07-21'),
  ('bird',3,'1970-05-24'),
  ('bird',3,'1970-05-24'),
  ('bird',2,'1970-06-07'),
  ('bird',2,'1970-06-13'),
  ('bird',1,'1970-06-24'),
  ('bird',0,'1970-09-16'),
  ('dog',1,'1971-04-22'),
  ('dog',1,'1971-05-04'),
  ('dog',1,'1971-05-05'),
  ('dog',1,'1971-05-05'),
  ('dog',1,'1971-05-06'),
  ('bird',1,'1972-05-06'),
  ('bird',5,'1972-05-07'),
  ('cat',1,'1972-05-19'),
  ('bird',1,'1972-05-19'),
  ('bird',1,'1972-05-20'),
  ('dog',1,'1972-05-24'),
  ('cat',1,'1972-07-29'),
  ('bird',1,'1973-05-05'),
  ('bird',1,'1973-05-06'),
  ('dog',2,'1973-05-17'),
  ('bird',1,'1973-05-18'),
  ('dog',1,'1973-05-21'),
  ('dog',1,'1973-05-28'),
  ('cat',1,'1973-06-24'),
  ('bird',4,'1974-05-16'),
  ('dog',1,'1974-05-23'),
  ('dog',4,'1974-05-24'),
  ('cat',1,'1974-06-16'),
  ('cat',3,'1974-06-16'),
  ('bird',1,'1974-07-04'),
  ('cat',1,'1975-05-25'),
  ('cat',2,'1975-05-25'),
  ('cat',1,'1975-06-23'),
  ('cat',1,'1975-06-23'),
  ('cat',1,'1975-07-02'),
  ('bird',2,'1975-07-08'),
  ('bird',12,'1976-05-23'),
  ('bird',12,'1976-05-23'),
  ('bird',4,'1976-05-30'),
  ('bird',4,'1976-05-30'),
  ('cat',1,'1976-07-20'),
  ('dog',1,'1977-01-01'),
  ('dog',1,'1977-04-26'),
  ('cat',1,'1977-05-09'),
  ('bird',3,'1977-06-06'),
  ('bird',7,'1977-06-06'),
  ('bird',3,'1977-06-07'),
  ('bird',1,'1977-06-07'),
  ('bird',6,'1977-06-07'),
  ('bird',1,'1977-06-09'),
  ('cat',2,'1977-06-20'),
  ('cat',3,'1977-06-20'),
  ('cat',2,'1977-06-22'),
  ('cat',1,'1977-06-22'),
  ('cat',1,'1977-06-24'),
  ('cat',1,'1977-06-24'),
  ('cat',1,'1977-07-21'),
  ('bird',1,'1978-05-18'),
  ('bird',4,'1978-05-18'),
  ('dog',1,'1978-05-27'),
  ('cat',1,'1978-05-28'),
  ('cat',1,'1978-05-28'),
  ('dog',1,'1978-05-30'),
  ('cat',2,'1978-05-30'),
  ('dog',1,'1978-05-31'),
  ('dog',3,'1978-06-01'),
  ('bird',1,'1978-06-15'),
  ('cat',1,'1978-06-15'),
  ('cat',4,'1978-06-15'),
  ('bird',1,'1978-06-22'),
  ('cat',2,'1978-06-22'),
  ('cat',2,'1978-06-23'),
  ('cat',10,'1978-06-24'),
  ('cat',1,'1978-07-06'),
  ('cat',1,'1978-07-06'),
  ('cat',1,'1978-07-09'),
  ('cat',1,'1978-07-09');
288 rows affected

create function get_first(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date, date_trunc('month', p_input)::date + 10, '[]')
$$
language sql
immutable;
create function get_second(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 11, date_trunc('month', p_input)::date + 20, '[]')
$$
language sql
immutable;
create function get_third(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 21, (date_trunc('month', p_input) + interval '1 month')::date, '[)')
$$
language sql
immutable;
SELECT animal, count(*), min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)), max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)),
	concat(
	'From ',
		CASE  
			WHEN cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 10 THEN 'beginning'
			WHEN cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 20 THEN 'mid'
			WHEn cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 31 THEN 'end'
		END
		, ' of '
		, TO_CHAR(TO_DATE (cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',1) as integer)::text, 'MM'), 'FMmonth' )
	, ' till ',
		CASE  
			WHEN cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 10 THEN 'beginning'
			WHEN cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 20 THEN 'mid'
			WHEn cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 31 THEN 'end'
		END
		, ' of '
		, TO_CHAR(TO_DATE (cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',1) as integer)::text, 'MM'), 'FMmonth' )
	,'.')
	
	FROM occurrence 
GROUP BY animal
ORDER BY animal


animal
count
min
max
concat


bird
113
4.17
9.16
From mid of april till mid of september.

cat
72
1.1
7.9
From beginning of january till beginning of july.

dog
103
1.1
9.22
From beginning of january till end of september.
animal	count	min	max	concat
bird	113	4.17	9.16	From mid of april till mid of september.
cat	72	1.1	7.9	From beginning of january till beginning of july.
dog	103	1.1	9.22	From beginning of january till end of september.