Postgres 11

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 c_account_p1
(
    billing_account_guid character varying(40)  NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    ban integer NOT NULL,
    CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
) PARTITION by RANGE(load_dttm);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "c_account_p1" lacks column "load_dttm" which is part of the partition key.


CREATE TABLE c_account_p2
(
    billing_account_guid character varying(40)  NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    ban integer NOT NULL
    
) PARTITION by RANGE(load_dttm);

CREATE TABLE


CREATE TABLE c_account_p1
(
    billing_account_guid character varying(40)  NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    ban integer NOT NULL,
    CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid,load_dttm, ban)
) PARTITION by RANGE(load_dttm);

CREATE TABLE