Postgres 9.6

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 foo(
    id SERIAL PRIMARY KEY,
    will_be_propagated BOOLEAN NOT NULL,
    target TEXT
);
ALTER TABLE foo
    ADD CONSTRAINT foo__id_prop__un UNIQUE(id, will_be_propagated),
    ADD CONSTRAINT foo__id_prop_target__un UNIQUE(id, will_be_propagated, target)
;
CREATE TABLE foo_watcher(
    id SERIAL PRIMARY KEY,
    description TEXT NOT NULL,
    foo_id INTEGER NOT NULL,
    foo_will_be_propagated BOOLEAN NOT NULL,
    foo_target TEXT
);
ALTER TABLE foo_watcher
    ADD CONSTRAINT foo_w__foo_id_foo_prop__fk
        FOREIGN KEY (foo_id, foo_will_be_propagated) REFERENCES foo(id, will_be_propagated)
        ON UPDATE RESTRICT,
    ADD CONSTRAINT foo_w__foo_id_foo_prop_foo_target
        FOREIGN KEY (foo_id, foo_will_be_propagated, foo_target) REFERENCES foo(id, will_be_propagated, target)
        ON UPDATE CASCADE
;
INSERT INTO foo(will_be_propagated, target) VALUES
    (TRUE, 'Hello'),
    (TRUE, 'World'),
    (FALSE, '<NULL>'),
    (FALSE, '<NULL>')
;
SELECT * FROM foo;
4 rows affected



id
will_be_propagated
target


1
t
Hello

2
t
World

3
f
<NULL>

4
f
<NULL>


INSERT INTO foo_watcher(description, foo_id , foo_will_be_propagated, foo_target)
    SELECT 'watcher_' || id, id , will_be_propagated, target
    FROM foo
;
SELECT * FROM foo_watcher;
4 rows affected



id
description
foo_id
foo_will_be_propagated
foo_target


1
watcher_1
1
t
Hello

2
watcher_2
2
t
World

3
watcher_3
3
f
<NULL>

4
watcher_4
4
f
<NULL>


UPDATE foo SET target = 'Alpha' WHERE id = 1;
UPDATE foo SET target = 'Beta'  WHERE id = 2;
UPDATE foo SET target = 'Gamma' WHERE id = 3;
UPDATE foo SET target = 'Delta' WHERE id = 4;
SELECT * FROM foo;
1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
will_be_propagated
target


1
t
Alpha

2
t
Beta

3
f
Gamma

4
f
Delta


SELECT * FROM foo_watcher;


id
description
foo_id
foo_will_be_propagated
foo_target


1
watcher_1
1
t
Alpha

2
watcher_2
2
t
Beta

3
watcher_3
3
f
Gamma

4
watcher_4
4
f
Delta

id	description	foo_id	foo_will_be_propagated	foo_target
1	watcher_1	1	t	Hello
2	watcher_2	2	t	World
3	watcher_3	3	f	<NULL>
4	watcher_4	4	f	<NULL>

id	description	foo_id	foo_will_be_propagated	foo_target
1	watcher_1	1	t	Alpha
2	watcher_2	2	t	Beta
3	watcher_3	3	f	Gamma
4	watcher_4	4	f	Delta