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 door(title)as values
 ('DR-1')
,('DR-01')
,('DR-02')
,('DR-03')
,('DR-04')
,('DR-100')
,('DR-1001')
,('DR-101')
,('DR-102')
,('DR-104')
,('Entrance-1')
,('Entrance-2')
,('MY-Prefix-10-Entrance-1')
,('MY-Prefix-11-Entrance-1')
,('MY-Prefix-19-Entrance-1')
,('MY-Prefix-1-Entrance-1')
,('MY-Prefix-20-Entrance-1')
,('MY-Prefix-2-Entrance-1')
,('MY-Prefix-3-Entrance-1')
,('MY-Prefix-8-Entrance-1')
,('MY-Prefix-9-Entrance-1')
,('MY-Prefix-9-Entrance-2')
,('MY-Prefix-9-Entrance-3')
;

CREATE COLLATION numerical( provider=icu
                           ,deterministic=false
                           ,locale='und-u-kn' );
SELECT title
FROM door
ORDER BY title COLLATE numerical;

SELECT 23

CREATE COLLATION

title

DR-1

DR-01

DR-02

DR-03

DR-04

DR-100

DR-101

DR-102

DR-104

DR-1001

Entrance-1

Entrance-2

MY-Prefix-1-Entrance-1

MY-Prefix-2-Entrance-1

MY-Prefix-3-Entrance-1

MY-Prefix-8-Entrance-1

MY-Prefix-9-Entrance-1

MY-Prefix-9-Entrance-2

MY-Prefix-9-Entrance-3

MY-Prefix-10-Entrance-1

MY-Prefix-11-Entrance-1

MY-Prefix-19-Entrance-1

MY-Prefix-20-Entrance-1

SELECT 23

--more data to test performance
select setseed(.42);

insert into door
select concat_ws('-'
                 ,(array['DR','Entrance','MY-Prefix','Some.Thing=Else'])[(1+(random()*3)::int)]
                 ,(random()*100)::int
                 ,case when random()>.8 then 'Entrance' end
                 ,case when random()>.8 then (random()*100)::int end)
from generate_series(1,5e5);

select*from door tablesample bernoulli(.42)repeatable(.43)limit 9;

setseed

SELECT 1

INSERT 0 500000

title

Entrance-64

DR-8-Entrance

MY-Prefix-66

MY-Prefix-45-90

Some.Thing=Else-15

MY-Prefix-85-Entrance

DR-78

DR-44

Entrance-11

SELECT 9

--https://stackoverflow.com/a/79255585/5298879
explain analyze verbose
select title
    -- , REGEXP_REPLACE(title, '[[:digit:]]+', '', 'g')::text as chars 
    -- , REGEXP_REPLACE(title, '[^[:digit:]]+', '', 'g')::integer as numbers
from door order by 
REGEXP_REPLACE(title, '[[:digit:]]+', '', 'g')::text,
REGEXP_REPLACE(title, '[^[:digit:]]+', '', 'g')::integer;

QUERY PLAN

Sort  (cost=61747.22..62705.34 rows=383248 width=68) (actual time=2525.014..2710.176 rows=500023 loops=1)

  Output: title, (regexp_replace(title, '[[:digit:]]+'::text, ''::text, 'g'::text)), ((regexp_replace(title, '[^[:digit:]]+'::text, ''::text, 'g'::text))::integer)

  Sort Key: (regexp_replace(door.title, '[[:digit:]]+'::text, ''::text, 'g'::text)), ((regexp_replace(door.title, '[^[:digit:]]+'::text, ''::text, 'g'::text))::integer)

  Sort Method: external merge  Disk: 20792kB

  ->  Seq Scan on public.door  (cost=0.00..10482.96 rows=383248 width=68) (actual time=0.240..1607.123 rows=500023 loops=1)

        Output: title, regexp_replace(title, '[[:digit:]]+'::text, ''::text, 'g'::text), (regexp_replace(title, '[^[:digit:]]+'::text, ''::text, 'g'::text))::integer

Planning Time: 0.164 ms

Execution Time: 2746.524 ms

EXPLAIN

--https://stackoverflow.com/a/79255853/5298879
explain analyze verbose
SELECT
  title
FROM
  door
ORDER BY
  REGEXP_REPLACE(title, '^(\D*).*', '\1') NULLS FIRST,
  NULLIF(REGEXP_REPLACE(title, '^\D*(\d*).*', '\1'), '')::INTEGER NULLS FIRST,
  REGEXP_REPLACE(title, '^\D*\d*(\D*).*', '\1') NULLS FIRST,
  NULLIF(REGEXP_REPLACE(title, '^\D*\d*\D*(\d*).*', '\1'), '')::INTEGER NULLS FIRST,
  title DESC;

QUERY PLAN

Gather Merge  (cost=44083.21..70008.81 rows=225440 width=104) (actual time=5826.154..6264.712 rows=500023 loops=1)

  Output: title, (regexp_replace(title, '^(\D*).*'::text, '\1'::text)), ((NULLIF(regexp_replace(title, '^\D*(\d*).*'::text, '\1'::text), ''::text))::integer), (regexp_replace(title, '^\D*\d*(\D*).*'::text, '\1'::text)), ((NULLIF(regexp_replace(title, '^\D*\d*\D*(\d*).*'::text, '\1'::text), ''::text))::integer)

  Workers Planned: 1

  Workers Launched: 1

  ->  Sort  (cost=43083.20..43646.80 rows=225440 width=104) (actual time=5810.013..5960.143 rows=250012 loops=2)

        Output: title, (regexp_replace(title, '^(\D*).*'::text, '\1'::text)), ((NULLIF(regexp_replace(title, '^\D*(\d*).*'::text, '\1'::text), ''::text))::integer), (regexp_replace(title, '^\D*\d*(\D*).*'::text, '\1'::text)), ((NULLIF(regexp_replace(title, '^\D*\d*\D*(\d*).*'::text, '\1'::text), ''::text))::integer)

        Sort Key: (regexp_replace(door.title, '^(\D*).*'::text, '\1'::text)) NULLS FIRST, ((NULLIF(regexp_replace(door.title, '^\D*(\d*).*'::text, '\1'::text), ''::text))::integer) NULLS FIRST, (regexp_replace(door.title, '^\D*\d*(\D*).*'::text, '\1'::text)) NULLS FIRST, ((NULLIF(regexp_replace(door.title, '^\D*\d*\D*(\d*).*'::text, '\1'::text), ''::text))::integer) NULLS FIRST, door.title DESC

        Sort Method: external merge  Disk: 10944kB

        Worker 0:  actual time=5794.181..5943.619 rows=250755 loops=1

          Sort Method: external merge  Disk: 11016kB

        ->  Parallel Seq Scan on public.door  (cost=0.00..10708.40 rows=225440 width=104) (actual time=0.237..4988.437 rows=250012 loops=2)

              Output: title, regexp_replace(title, '^(\D*).*'::text, '\1'::text), (NULLIF(regexp_replace(title, '^\D*(\d*).*'::text, '\1'::text), ''::text))::integer, regexp_replace(title, '^\D*\d*(\D*).*'::text, '\1'::text), (NULLIF(regexp_replace(title, '^\D*\d*\D*(\d*).*'::text, '\1'::text), ''::text))::integer

              Worker 0:  actual time=0.293..4965.330 rows=250755 loops=1

Planning Time: 0.111 ms

Execution Time: 6288.836 ms

EXPLAIN

explain analyze verbose 
SELECT title
FROM door
ORDER BY title COLLATE numerical;

QUERY PLAN

Sort  (cost=56602.24..57560.36 rows=383248 width=64) (actual time=1108.225..1286.453 rows=500023 loops=1)

  Output: title, ((title)::text)

  Sort Key: door.title COLLATE numerical

  Sort Method: external merge  Disk: 19440kB

  ->  Seq Scan on public.door  (cost=0.00..6650.48 rows=383248 width=64) (actual time=0.052..53.273 rows=500023 loops=1)

        Output: title, title

Planning Time: 0.067 ms

Execution Time: 1317.508 ms

EXPLAIN