Postgres 16

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?.
--https://stackoverflow.com/q/78294401/5298879
/*
split with multiple delimiters sql

I have a use case where i need to split strings by multiple delimiters.
*/
create table mytbl(client_id) as values
 ('blah_blah')
,('bleh-bleh')
;

SELECT 2

--https://stackoverflow.com/a/78294495/5298879
select client_id
  , split_part(client_id2,'-',1)
  , split_part(client_id2,'-',2)
from mytbl cross join lateral
(values(translate(client_id,'_;/','---'))) as v(client_id2)

client_id
split_part
split_part

blah_blah
blah
blah

bleh-bleh
bleh
bleh

SELECT 2

select setseed(.42);--stabilise random() between calls
--generate samples:
insert into mytbl 
select replace(gen_random_uuid()::text
               ,(array['a','b','c','d'])[1+(random()*3)::int]
               ,(array[',',';','/','_'])[1+(random()*3)::int])
from generate_series(1,3e5);
--inspect samples:
select * from mytbl tablesample bernoulli(.42)repeatable(.42)limit 10;

setseed

SELECT 1

INSERT 0 300000

client_id

100528b4-b60;-4039-8b62-1e033;26a2d4

ad72a356-1546-49fc-;ef6-a0d1;f5d7f60

503aa3dd-_c14-4311-a514-90a45f023e2_

9;5d096d-c149-41fc-90c4-b109;8165115

c178_649-be95-4e6_-b535-9504f10e1300

/ce3/284-bbbb-4d/2-b7d1-000b0ff4f12d

6ff/0399-7a63-4293-8d1/-/68579a05/7a

82f/3868-aa8e-461f-9104-1789cc630/a4

2e/7ebe1-b90f-47/0-88c8-9460e7f6459/

34b2ead8-2328-4103-a9f4-5e51b3668fd;

SELECT 10

--https://stackoverflow.com/a/78294495/5298879
explain analyze verbose
select client_id
  , split_part(client_id2,'-',1)
  , split_part(client_id2,'-',2)
from mytbl cross join lateral
(values(translate(client_id,'_;/','---'))) as v(client_id2);

select client_id
  , split_part(client_id2,'-',1)
  , split_part(client_id2,'-',2)
from mytbl cross join lateral
(values(translate(client_id,'_;/','---'))) as v(client_id2)
limit 5;

QUERY PLAN

Seq Scan on public.mytbl  (cost=0.00..9303.72 rows=340136 width=96) (actual time=0.039..833.565 rows=300002 loops=1)

  Output: mytbl.client_id, split_part(translate(mytbl.client_id, '_;/'::text, '---'::text), '-'::text, 1), split_part(translate(mytbl.client_id, '_;/'::text, '---'::text), '-'::text, 2)

Planning Time: 0.100 ms

Execution Time: 845.496 ms

EXPLAIN

client_id
split_part
split_part

blah_blah
blah
blah

bleh-bleh
bleh
bleh

;0149804-d95;-4b78-912d-b7dd13df1427

0149804

8c30096b-42ac-4f1e-a7bc-a37,19232280
8c30096b
42ac

31968;9e-b;ed-45ce-;22f-5780c2cd3f;1
31968
9e

SELECT 5

--as posted, https://stackoverflow.com/revisions/78294440/1 is syntactically invalid
explain analyze verbose
select 
    client_id,
    regexp_split_to_array(client_id, '[-_]')[0] col1, 
    regexp_split_to_array(client_id, '[-_]')[1] col2 
from mytbl;

ERROR:  syntax error at or near "["
LINE 5:     regexp_split_to_array(client_id, '[-_]')[0] col1, 
                                                    ^

--fixing the syntax error shows the incorrect subscripting 
--(regular arrays are 1-based)
explain analyze verbose
select 
    client_id,
    (regexp_split_to_array(client_id, '[-_]'))[0] col1, 
    (regexp_split_to_array(client_id, '[-_]'))[1] col2 
from mytbl;

select 
    client_id,
    (regexp_split_to_array(client_id, '[-_]'))[0] col1, 
    (regexp_split_to_array(client_id, '[-_]'))[1] col2 
from mytbl limit 5;

QUERY PLAN

Seq Scan on public.mytbl  (cost=0.00..7603.04 rows=340136 width=96) (actual time=0.139..2616.275 rows=300002 loops=1)

  Output: client_id, (regexp_split_to_array(client_id, '[-_]'::text))[0], (regexp_split_to_array(client_id, '[-_]'::text))[1]

Planning Time: 0.023 ms

Execution Time: 2633.003 ms

EXPLAIN

client_id
col1
col2

blah_blah
null
blah

bleh-bleh
null
bleh

;0149804-d95;-4b78-912d-b7dd13df1427
null
;0149804

8c30096b-42ac-4f1e-a7bc-a37,19232280
null
8c30096b

31968;9e-b;ed-45ce-;22f-5780c2cd3f;1
null
31968;9e

SELECT 5

--fixing that you arrive at a method works
--but its just slower due to regex and array overheads
explain analyze verbose
select 
    client_id,
    (regexp_split_to_array(client_id, '[-_]'))[1] col1, 
    (regexp_split_to_array(client_id, '[-_]'))[2] col2 
from mytbl;

select 
    client_id,
    (regexp_split_to_array(client_id, '[-_]'))[1] col1, 
    (regexp_split_to_array(client_id, '[-_]'))[2] col2 
from mytbl limit 5;

QUERY PLAN

Seq Scan on public.mytbl  (cost=0.00..7603.04 rows=340136 width=96) (actual time=0.034..3023.075 rows=300002 loops=1)

  Output: client_id, (regexp_split_to_array(client_id, '[-_]'::text))[1], (regexp_split_to_array(client_id, '[-_]'::text))[2]

Planning Time: 0.022 ms

Execution Time: 3042.441 ms

EXPLAIN

client_id
col1
col2

blah_blah
blah
blah

bleh-bleh
bleh
bleh

;0149804-d95;-4b78-912d-b7dd13df1427
;0149804
d95;

8c30096b-42ac-4f1e-a7bc-a37,19232280
8c30096b
42ac

31968;9e-b;ed-45ce-;22f-5780c2cd3f;1
31968;9e
b;ed

SELECT 5

--https://stackoverflow.com/a/78294494/5298879
--Stefanov.sm's cte is easier on the eye, 
--but neither the CTE nor the simpler regular expression helps performance
--(mind the downvote wasn't mine)
explain analyze verbose
with t as (
 select client_id, regexp_split_to_array(client_id, '-|_') arr
 from mytbl
)
select client_id, arr[1] col1, arr[2] col2
from t;

with t as (
 select client_id, regexp_split_to_array(client_id, '-|_') arr
 from mytbl
)
select client_id, arr[1] col1, arr[2] col2
from t
limit 5;

QUERY PLAN

Seq Scan on public.mytbl  (cost=0.00..7603.04 rows=340136 width=96) (actual time=0.055..2669.305 rows=300002 loops=1)

  Output: mytbl.client_id, (regexp_split_to_array(mytbl.client_id, '-|_'::text))[1], (regexp_split_to_array(mytbl.client_id, '-|_'::text))[2]

Planning Time: 0.043 ms

Execution Time: 2685.234 ms

EXPLAIN

client_id
col1
col2

blah_blah
blah
blah

bleh-bleh
bleh
bleh

;0149804-d95;-4b78-912d-b7dd13df1427
;0149804
d95;

8c30096b-42ac-4f1e-a7bc-a37,19232280
8c30096b
42ac

31968;9e-b;ed-45ce-;22f-5780c2cd3f;1
31968;9e
b;ed

SELECT 5

--the quick one again, after the db is pre-warmed
--https://stackoverflow.com/a/78294495/5298879
explain analyze verbose
select client_id
  , split_part(client_id2,'-',1)
  , split_part(client_id2,'-',2)
from mytbl cross join lateral
(values(translate(client_id,'_;/','---'))) as v(client_id2);

select client_id
  , split_part(client_id2,'-',1)
  , split_part(client_id2,'-',2)
from mytbl cross join lateral
(values(translate(client_id,'_;/','---'))) as v(client_id2)
limit 5;

QUERY PLAN

Seq Scan on public.mytbl  (cost=0.00..9303.72 rows=340136 width=96) (actual time=0.011..830.449 rows=300002 loops=1)

  Output: mytbl.client_id, split_part(translate(mytbl.client_id, '_;/'::text, '---'::text), '-'::text, 1), split_part(translate(mytbl.client_id, '_;/'::text, '---'::text), '-'::text, 2)

Planning Time: 0.036 ms

Execution Time: 842.286 ms

EXPLAIN

client_id
split_part
split_part

blah_blah
blah
blah

bleh-bleh
bleh
bleh

;0149804-d95;-4b78-912d-b7dd13df1427

0149804

8c30096b-42ac-4f1e-a7bc-a37,19232280
8c30096b
42ac

31968;9e-b;ed-45ce-;22f-5780c2cd3f;1
31968
9e

SELECT 5
client_id	split_part	split_part
blah_blah	blah	blah
bleh-bleh	bleh	bleh
;0149804-d95;-4b78-912d-b7dd13df1427		0149804
8c30096b-42ac-4f1e-a7bc-a37,19232280	8c30096b	42ac
31968;9e-b;ed-45ce-;22f-5780c2cd3f;1	31968	9e
client_id	col1	col2
blah_blah	null	blah
bleh-bleh	null	bleh
;0149804-d95;-4b78-912d-b7dd13df1427	null	;0149804
8c30096b-42ac-4f1e-a7bc-a37,19232280	null	8c30096b
31968;9e-b;ed-45ce-;22f-5780c2cd3f;1	null	31968;9e