Postgres 15

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 forms (
    id serial PRIMARY KEY,
    account INT NOT NULL,
    author text,
    type INT NOT NULL
);
CREATE UNIQUE INDEX idx ON forms ((CASE WHEN type = 11 THEN type END));

CREATE TABLE



CREATE INDEX


INSERT INTO  forms(account, author, type) VALUES 
(1, 'Иванов', 11)

INSERT 0 1


INSERT INTO  forms(account, author, type) VALUES 
(1, 'Сидоров', 11);

ERROR:  duplicate key value violates unique constraint "idx"
DETAIL:  Key ((
CASE
    WHEN type = 11 THEN type
    ELSE NULL::integer
END))=(11) already exists.


INSERT INTO  forms(account, author, type) VALUES 
(1, 'Иванов', 12),
(1, 'Сидоров', 12);

INSERT 0 2


SELECT * FROM forms;


id
account
author
type


1
1
Иванов
11

3
1
Иванов
12

4
1
Сидоров
12



SELECT 3