Postgres 11

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 data
(
  id    int not null,
  label varchar(50)
);

insert into data (id, label) values (1, 'DISK 2');
insert into data (id, label) values (2, 'DISK 5');
insert into data (id, label) values (3, 'DISK 33');
insert into data (id, label) values (4, 'DISK 7');
insert into data (id, label) values (5, 'DISK 15');
insert into data (id, label) values (6, 'DISK 27');
insert into data (id, label) values (7, 'DISK 8');
insert into data (id, label) values (8, 'DISK 10');
insert into data (id, label) values (9, 'DISK 1');
insert into data (id, label) values (10, 'DISK 3');
insert into data (id, label) values (11, 'DISK 9');
insert into data (id, label) values (12, 'DISK 4');
insert into data (id, label) values (13, 'DISK 20');
insert into data (id, label) values (14, 'DISK 6');

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

-- Typical / standard string ordering (i.e. 'xx10' comes before 'xx2')
select * from data order by label asc;

id
label

9
DISK 1

8
DISK 10

5
DISK 15

1
DISK 2

13
DISK 20

6
DISK 27

10
DISK 3

3
DISK 33

12
DISK 4

2
DISK 5

14
DISK 6

4
DISK 7

7
DISK 8

11
DISK 9

SELECT 14

-- A custom collation using Unicode's "numeric" option is needed:
-- https://www.postgresql.org/docs/11/collation.html#id-1.6.10.4.5.7.5
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

CREATE COLLATION

-- Desired ordering, thanks to ICU
select * from data order by label COLLATE "numeric" asc;

id
label

9
DISK 1

1
DISK 2

10
DISK 3

12
DISK 4

2
DISK 5

14
DISK 6

4
DISK 7

7
DISK 8

11
DISK 9

8
DISK 10

5
DISK 15

13
DISK 20

6
DISK 27

3
DISK 33

SELECT 14

id	label
9	DISK 1
8	DISK 10
5	DISK 15
1	DISK 2
13	DISK 20
6	DISK 27
10	DISK 3
3	DISK 33
12	DISK 4
2	DISK 5
14	DISK 6
4	DISK 7
7	DISK 8
11	DISK 9