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 pencil_count (  -- A Table also registers the row type
  pencil_color varchar(30)
, count integer
);

CREATE TABLE pencils (
  id serial
, pencils_ pencil_count[]
);

-- Basic insert:
INSERT INTO pencil_count VALUES
  ('red' , 1)
, ('blue', 2);

CREATE TABLE

CREATE TABLE

INSERT 0 2

-- How to find out? Just ask Postgres:
SELECT p AS p_row FROM pencil_count p;

p_row

(red,1)

(blue,2)

SELECT 2

SELECT ARRAY(SELECT p FROM pencil_count p) AS p_row_arr;

p_row_arr

{"(red,1)","(blue,2)"}

SELECT 1

-- 4 ways to do it; the last is the least favorable alternative:
INSERT INTO pencils(pencils_)
VALUES
  (ARRAY ['(red,1)'::pencil_count, '(blue,2)'])
, (ARRAY ['(red,1)', '(blue,2)']::pencil_count[])
, ('{"(red,1)","(blue,2)"}')
, ('{"(\"blue\",1)","(\"red\",2)"}')
-- The last row is burdened with  redundant noise
RETURNING *;

id
pencils_

1
{"(red,1)","(blue,2)"}

2
{"(red,1)","(blue,2)"}

3
{"(red,1)","(blue,2)"}

4
{"(blue,1)","(red,2)"}

INSERT 0 4

id	pencils_
1	{"(red,1)","(blue,2)"}
2	{"(red,1)","(blue,2)"}
3	{"(red,1)","(blue,2)"}
4	{"(blue,1)","(red,2)"}