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?.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE tab
(
  ln TEXT
);
with cte as (
select 'LINESTRING ( 1.0 2.0, 3 4)'                               as txt  UNion all
select 'LINESTRING M ( 1 2 3, 4 5 6.0)'                           as txt  union all
select 'LINESTRING ( 1 2, 3 4, 5 6)'                              as txt  union all
select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)'               as txt  union all
select 'LINESTRING M ( 1 2 3.0, 4 5 6)'                           as txt  union all
select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt 
)
INSERT INTO tab
select
    txt
from
    cte;
6 rows affected

SELECT * FROM tab;


ln


LINESTRING ( 1.0 2.0, 3 4)

LINESTRING M ( 1 2 3, 4 5 6.0)

LINESTRING ( 1 2, 3 4, 5 6)

LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)

LINESTRING M ( 1 2 3.0, 4 5 6)

MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))


WITH cte AS
(
  SELECT 
    TRANSLATE(ln, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ()', '') AS tr
  FROM
    tab
), x AS
(
  SELECT
    TRIM(REGEXP_SPLIT_TO_TABLE(tr, ',')) AS sp 
  FROM
    cte
)
SELECT * FROM x;


sp


1.0 2.0

3 4

1 2 3

4 5 6.0

1 2

3 4

5 6

1 2 3

4 5 6.0

7 8.00 9.0

1 2 3.0

4 5 6

1 2 3

4 5 6

7 8 9

10 11 12.0


WITH cte AS
(
  SELECT 
    TRANSLATE(ln, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ()', '') AS tr
  FROM
    tab
), x AS
(
  SELECT
    TRIM(REGEXP_SPLIT_TO_TABLE(tr, ',')) AS sp 
  FROM
    cte
)
SELECT 
  sp,
  a.elem,
  a.nr
FROM x
  LEFT JOIN LATERAL 
    UNNEST(STRING_TO_ARRAY(x.sp, ' ')) WITH ORDINALITY AS a(elem, nr)
    ON TRUE;


sp
elem
nr


1.0 2.0
1.0
1

1.0 2.0
2.0
2

3 4
3
1

3 4
4
2

1 2 3
1
1

1 2 3
2
2

1 2 3
3
3

4 5 6.0
4
1

4 5 6.0
5
2

4 5 6.0
6.0
3

1 2
1
1

1 2
2
2

3 4
3
1

3 4
4
2

5 6
5
1

5 6
6
2

1 2 3
1
1

1 2 3
2
2

1 2 3
3
3

4 5 6.0
4
1

4 5 6.0
5
2

4 5 6.0
6.0
3

7 8.00 9.0
7
1

7 8.00 9.0
8.00
2

7 8.00 9.0
9.0
3

1 2 3.0
1
1

1 2 3.0
2
2

1 2 3.0
3.0
3

4 5 6
4
1

4 5 6
5
2

4 5 6
6
3

1 2 3
1
1

1 2 3
2
2

1 2 3
3
3

4 5 6
4
1

4 5 6
5
2

4 5 6
6
3

7 8 9
7
1

7 8 9
8
2

7 8 9
9
3

10 11 12.0
10
1

10 11 12.0
11
2

10 11 12.0
12.0
3

sp	elem	nr
1.0 2.0	1.0	1
1.0 2.0	2.0	2
3 4	3	1
3 4	4	2
1 2 3	1	1
1 2 3	2	2
1 2 3	3	3
4 5 6.0	4	1
4 5 6.0	5	2
4 5 6.0	6.0	3
1 2	1	1
1 2	2	2
3 4	3	1
3 4	4	2
5 6	5	1
5 6	6	2
1 2 3	1	1
1 2 3	2	2
1 2 3	3	3
4 5 6.0	4	1
4 5 6.0	5	2
4 5 6.0	6.0	3
7 8.00 9.0	7	1
7 8.00 9.0	8.00	2
7 8.00 9.0	9.0	3
1 2 3.0	1	1
1 2 3.0	2	2
1 2 3.0	3.0	3
4 5 6	4	1
4 5 6	5	2
4 5 6	6	3
1 2 3	1	1
1 2 3	2	2
1 2 3	3	3
4 5 6	4	1
4 5 6	5	2
4 5 6	6	3
7 8 9	7	1
7 8 9	8	2
7 8 9	9	3
10 11 12.0	10	1
10 11 12.0	11	2
10 11 12.0	12.0	3