Postgres 13

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 strings(string text);
INSERT INTO strings VALUES
  ('I think Postgres is nifty')
, ('And it keeps getting better');
2 rows affected

SELECT *
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn);


word
rn


I
1

think
2

Postgres
3

is
4

nifty
5


-- Postgres 9.3 or older 
-- Quick & dirty:
SELECT *, row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid, unnest(string_to_array(string, ' ')) AS word
   FROM strings
   ) x;

-- Slow & sure:
SELECT arr[rn] AS word, rn
FROM  (
   SELECT *, generate_series(1, array_upper(arr, 1)) AS rn
   FROM  (
      SELECT string_to_array(string, ' ') AS arr
      FROM   strings
      ) x
   ) y;


ctid
word
rn


(0,1)
I
1

(0,1)
think
2

(0,1)
Postgres
3

(0,1)
is
4

(0,1)
nifty
5

(0,2)
And
1

(0,2)
it
2

(0,2)
keeps
3

(0,2)
getting
4

(0,2)
better
5




word
rn


I
1

think
2

Postgres
3

is
4

nifty
5

And
1

it
2

keeps
3

getting
4

better
5

ctid	word	rn
(0,1)	I	1
(0,1)	think	2
(0,1)	Postgres	3
(0,1)	is	4
(0,1)	nifty	5
(0,2)	And	1
(0,2)	it	2
(0,2)	keeps	3
(0,2)	getting	4
(0,2)	better	5