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?.

select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

-- create enum type genders
CREATE TYPE genders AS ENUM(
     'MALE', 'FEMALE'
);

CREATE TYPE

-- create people table
CREATE TABLE people(
    id VARCHAR(36) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender genders NOT NULL
);
-- Unique Constraints
ALTER TABLE people
    ADD CONSTRAINT people_fname_lname_uk UNIQUE (first_name, last_name);

CREATE TABLE

ALTER TABLE

-- list values of enum
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'genders';

enumlabel

MALE

FEMALE

SELECT 2

INSERT INTO people(id, first_name, last_name, gender) VALUES(gen_random_uuid(), 'Giri', 'Potte', 'MALE');

INSERT 0 1

INSERT INTO people(id, first_name, last_name, gender) VALUES(gen_random_uuid(), 'Boo', 'Potte', 'MALE');

INSERT 0 1

SELECT * FROM people;

id
first_name
last_name
gender

1528c876-6cd7-4925-b78c-4fac3cb4810b
Giri
Potte
MALE

e6feb8b8-5b4b-4858-af29-7a5d450b9c59
Boo
Potte
MALE

SELECT 2

id	first_name	last_name	gender
1528c876-6cd7-4925-b78c-4fac3cb4810b	Giri	Potte	MALE
e6feb8b8-5b4b-4858-af29-7a5d450b9c59	Boo	Potte	MALE