Postgres 13

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 IF NOT EXISTS cdr_event
(
    id bigint primary key generated always as identity,
    start_time timestamptz NOT NULL DEFAULT now(),
    end_time timestamptz NULL,
    group_id VARCHAR(10) NOT NULL,
    environment VARCHAR(10) NOT NULL,     
    level VARCHAR(10) NOT NULL,           
    schema VARCHAR(30) NOT NULL,      
    instance INTEGER NOT NULL,            
    hive_instance_db_name VARCHAR(100) GENERATED ALWAYS AS (group_id||'_'||environment||'_'||level||'_'||schema||'_'||instance::text) STORED,
    hive_static_db_name VARCHAR(100) GENERATED ALWAYS AS (group_id||'_'||environment||'_'||level||'_'||schema) STORED
);

CREATE TABLE


INSERT INTO cdr_event (group_id,environment,level,schema,instance)
VALUES ('grp1','env1','lev1','sch1',42);

INSERT 0 1


SELECT * FROM cdr_event;


id
start_time
end_time
group_id
environment
level
schema
instance
hive_instance_db_name
hive_static_db_name


1
2023-02-24 21:54:58.836707+00
null
grp1
env1
lev1
sch1
42
grp1_env1_lev1_sch1_42
grp1_env1_lev1_sch1



SELECT 1