Postgres 14

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 tbl (
  tbl_id  serial PRIMARY KEY
, version text NOT NULL
);

INSERT INTO tbl (version)
 VALUES
     ('14.1.1')
   , ('14.1.2')
   , ('14.1.Z')
   , ('14.1.1.1')
   , ('14.1.1.K')
   , ('14.1.Z.3.A');

INSERT INTO tbl (version)   -- 10k random rows
SELECT repeat((random()*100)::int::text || '.', g%7) || g%100 
FROM   generate_series (1,10000) g;

CREATE INDEX tbl_special_idx ON tbl ((length(version) - length(replace(version, '.', ''))), version COLLATE "C");
6 rows affected

10000 rows affected

VACUUM ANALYZE tbl;
SELECT *
FROM   tbl
WHERE  version LIKE '14.1.%'
AND    length(version) - length(replace(version, '.', '')) = 2;


tbl_id
version


1
14.1.1

2
14.1.2

3
14.1.Z