Postgres 9.6

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 (
   id      integer NOT NULL
 , version text NOT NULL
);

INSERT INTO tbl VALUES
     (10, '10-2')
   , (10, '10-1')
   , (10, '10')      -- missing subversion
   , (10, '10-111')  -- multi-digit number
   , (11, '11-1')
   , (11, '11-0')    -- proper '0'
   , (11, '11-')     -- missing subversion but trailing '-'
   , (11, '11-2');

INSERT 0 8

SELECT DISTINCT ON (id) *
FROM   tbl
ORDER  BY id, GREATEST(split_part(version, '-', 2), '0')::int DESC

id
version

10
10-111

11
11-2

SELECT 2

SELECT DISTINCT ON (id) *
FROM   tbl
ORDER  BY id, NULLIF(split_part(version, '-', 2), '')::int DESC NULLS LAST;

id
version

10
10-111

11
11-2

SELECT 2

SELECT DISTINCT ON (id) *
FROM   tbl
ORDER  BY id, CASE WHEN split_part(version, '-', 2)= '' THEN '0' ELSE split_part(version, '-', 2) END::int DESC;

id
version

10
10-111

11
11-2

SELECT 2

SELECT DISTINCT ON (id) *
FROM  (SELECT *, split_part(version, '-', 2) AS subversion FROM tbl) t
ORDER  BY id, CASE WHEN subversion = '' THEN '0' ELSE subversion END::int DESC;

id
version
subversion

10
10-111
111

11
11-2
2

SELECT 2