Postgres 9.5

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 taxrate (
   taxrate int PRIMARY KEY
);

CREATE TABLE taxrate_standard(
   taxrate int PRIMARY KEY REFERENCES taxrate
);

CREATE UNIQUE INDEX taxrate_standard_singleton ON taxrate_standard ((true));  -- makes it a singleton
CREATE INDEX

INSERT INTO taxrate (taxrate) VALUES (42);
INSERT INTO taxrate_standard (taxrate) VALUES (42);
INSERT 0 1

INSERT INTO taxrate (taxrate) VALUES (1),  (2);
INSERT 0 2

CREATE VIEW taxrate_combined AS
SELECT t.*, (ts.taxrate = t.taxrate) AS standard
FROM   taxrate t
LEFT   JOIN taxrate_standard ts USING (taxrate);

TABLE  taxrate_combined;


taxrate
standard


42
t

1
null

2
null


SELECT 3

`taxrate`	`standard`
`42`	`t`
`1`	`null`
`2`	`null`