Postgres 12

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 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE items
(
  item TEXT NOT NULL PRIMARY KEY
);
INSERT INTO items VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g');
7 rows affected

CREATE TABLE id_array
(
  the_arr INT[] 
);
INSERT INTO id_array VALUES
(ARRAY[3,5,11]);
1 rows affected

SELECT item, ROW_NUMBER() OVER (ORDER BY item ASC)
FROM items
ORDER BY item ASC;


item
row_number


a
1

b
2

c
3

d
4

e
5

f
6

g
7


SELECT
  * 
FROM UNNEST
(
  (SELECT the_arr FROM id_array)
) WITH ORDINALITY AS t (elem, nr);


elem
nr


3
1

5
2

11
3


SELECT 
  tab1.item, tab1.rn,
  tab2.elem, tab2.nr
FROM
(
  SELECT i.item, ROW_NUMBER() OVER (ORDER BY item ASC) AS rn
  FROM items i
  ORDER BY i.item
) AS tab1
JOIN
(
  SELECT
    * 
  FROM 
  UNNEST
  (
    (SELECT the_arr FROM id_array)
  ) WITH ORDINALITY AS t (elem, nr)
) AS tab2
ON tab1.rn  = tab2.nr;


item
rn
elem
nr


a
1
3
1

b
2
5
2

c
3
11
3