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 companies
(
    id               bigserial primary key,
    created_at       timestamp(6),  
    name             varchar(200),   
    status           varchar(200)
);
/*
select setseed(.42);
insert into companies (created_at,name,status)
select now()-random()*'4 months'::interval,
       left(gen_random_uuid()::text,floor(random()*15)::int),
       case when random()>.5 then 'Active' else 'Blocked' end
from generate_series(1,15,1)
returning *;
  */
insert into companies (created_at,name,status) values
  ('2023-10-07 18:06:55.9','Right before custom October start','Active'),
  ('2023-10-07 18:06:56.01','Right after custom October start','Blocked'),
  (now()-'1year'::interval-random()*'4 months'::interval,
   left(gen_random_uuid()::text,floor(random()*15)::int),
   case when random()>.5 then 'Active' else 'Blocked' end),
  (now()-'1year'::interval-random()*'4 months'::interval,
   left(gen_random_uuid()::text,floor(random()*15)::int),
   case when random()>.5 then 'Active' else 'Blocked' end)
returning *;

CREATE TABLE

id	created_at	name	status
1	2023-10-07 18:06:55.9	Right before custom October start	Active
2	2023-10-07 18:06:56.01	Right after custom October start	Blocked
3	2022-11-03 17:53:38.894278	a5b4172	Blocked
4	2022-10-03 14:52:14.827078	2bfc05d9-25f4-	Active

INSERT 0 4

created_at	Active	Blocked
2022-09-07 18:06:56	1	0
2022-10-07 18:06:56	0	1
2023-09-07 18:06:56	1	0
2023-10-07 18:06:56	0	1

SELECT 4