Postgres 16

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 mytable (
  start_date date,
  end_date date
);

insert into mytable values
('2023-09-01', '2023-09-20'),
('2023-10-01', '2023-10-20'),
('2023-10-01', '2024-01-07'),
('2023-12-01', '2024-01-20');

CREATE TABLE

INSERT 0 4

SELECT date_trunc('year', now()) + interval '9 month' 
       - (case when extract(month from current_date) < 10 then 1 else 0 end || ' year')::interval  
AS last_october;

last_october

2023-10-01 00:00:00+00

SELECT 1

SELECT *
FROM mytable
WHERE (start_date, end_date) OVERLAPS (date_trunc('year', now()) + interval '9 month' 
       - (case when extract(month from current_date) < 10 then 1 else 0 end || ' year')::interval , CURRENT_DATE);

start_date
end_date

2023-10-01
2023-10-20

2023-10-01
2024-01-07

2023-12-01
2024-01-20

SELECT 3

start_date	end_date
2023-10-01	2023-10-20
2023-10-01	2024-01-07
2023-12-01	2024-01-20