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 base_table
(
column1 varchar(255) NOT NULL,
column2 integer NOT NULL,
user_id bigint NOT NULL
)
PARTITION BY HASH (user_id);
CREATE UNIQUE INDEX idx_one ON base_table (column1, column2, user_id);
CREATE TABLE
CREATE INDEX
select *
from pg_indexes
where tablename = 'base_table';
| schemaname | tablename | indexname | tablespace | indexdef |
|---|---|---|---|---|
| public | base_table | idx_one | null | CREATE UNIQUE INDEX idx_one ON ONLY public.base_table USING btree (column1, column2, user_id) |
SELECT 1