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 music (
id serial primary key,
common1 int not null,
common2 boolean not null
);
create table symphony (
layers int not null,
at1 text not null
) inherits(music);
create table concerto (
lead text not null,
strings integer not null
) inherits(music);
CREATE TABLE
CREATE TABLE
CREATE TABLE
insert into concerto (common1, common2, lead, strings)
values (1, true, 'a', 5);
insert into symphony (common1, common2, layers, at1)
values (2, false, 3, 'b');
insert into music (common1, common2)
values (3, true);
INSERT 0 1
INSERT 0 1
INSERT 0 1
-- Fetches id, common1, and common2 from all rows.
select *
from music
| id | common1 | common2 |
|---|---|---|
| 3 | 3 | t |
| 2 | 2 | f |
| 1 | 1 | t |
SELECT 3
-- Fetches id, common1, and common2 from only the one row in music.
select *
from only music
| id | common1 | common2 |
|---|---|---|
| 3 | 3 | t |
SELECT 1
-- Fetches id, common1, common2, layers, at1 only from symphony
select *
from symphony
| id | common1 | common2 | layers | at1 |
|---|---|---|---|---|
| 2 | 2 | f | 3 | b |
SELECT 1