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_legacy
(
fordate date not null,
key2 int not null,
value int not null
)
partition by range (fordate);
create table mytable_200003
(
fordate date not null,
key2 int not null,
value int not null
);
insert into mytable_200003 (fordate, key2, value)
values
('2000-03-02', 1, 19),
('2000-03-30', 15, 8);
alter table mytable_legacy
attach partition mytable_200003
for values from ('2000-03-01') to ('2000-04-01');
CREATE TABLE
CREATE TABLE
INSERT 0 2
ALTER TABLE
select *
from mytable_legacy;
| fordate | key2 | value |
|---|---|---|
| 2000-03-02 | 1 | 19 |
| 2000-03-30 | 15 | 8 |
SELECT 2