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?.

select version();


version


PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE people (
    name TEXT PRIMARY KEY,
    age  INT  NOT NULL
);

INSERT INTO people VALUES
    ('Lisa',     30),
    ('Marta',    27),
    ('John',     32),
    ('Sam',      41),
    ('Alex',     12),
    ('Aristides',43),
    ('Cindi',     1)
    ;
7 rows affected

CREATE TABLE houses (
    house_name TEXT NOT NULL,
    house_member TEXT NOT NULL REFERENCES people(name)
);    

INSERT INTO houses VALUES
        ('house Smith', 'Lisa'),
        ('house Smith', 'Marta'),
        ('house Smith', 'John'),
        ('house Doe', 'Lisa'),
        ('house Doe', 'Marta'),
        ('house Doe', 'Alex'),
        ('house Doe', 'Sam'),
        ('house McKenny', 'Aristides'),
        ('house McKenny', 'John'),
        ('house McKenny', 'Cindi')
        ;
10 rows affected

SELECT DISTINCT A.name as child
FROM people A, people B
WHERE A.age + 12 < B.age;


child


Alex

Cindi

Lisa

Marta