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 test (val varchar) ;

CREATE TABLE

CREATE INDEX IF NOT EXISTS x ON test USING Btree (EXTRACT(EPOCH FROM val :: timestamp without time zone)) ;

ERROR:  functions in index expression must be marked IMMUTABLE

CREATE OR REPLACE FUNCTION epoch_immutable(val varchar)
RETURNS bigint LANGUAGE sql IMMUTABLE AS $$
SELECT EXTRACT(EPOCH FROM val :: TIMESTAMP WITHOUT TIME ZONE) ; $$ ;

CREATE FUNCTION

CREATE INDEX IF NOT EXISTS x ON test USING Btree (epoch_immutable(val)) ;

CREATE INDEX

INSERT INTO test VALUES ('20200101'), ('20231231 12:12:12')

INSERT 0 2