Postgres 11

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 "products" (
  "product_id" TEXT PRIMARY KEY
);

CREATE TABLE "products_ext" (
  "product_id" TEXT NOT NULL,
  "key" TEXT NOT NULL,
  "value" JSON,
  PRIMARY KEY ("product_id", "key"),
  FOREIGN KEY ("product_id") REFERENCES "products"("product_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
  );
  
  INSERT INTO "products" ("product_id") VALUES
  ('test1'),
  ('test2');

SELECT * FROM products;

INSERT INTO "products_ext" (product_id, "key", "value") VALUES
  ('test1', 'foo', '"Foo"'),
  ('test1', 'bar', '"Bar"'),
  ('test2', 'foo', '"Foo"');

CREATE TABLE

CREATE TABLE

INSERT 0 2

product_id

test1

test2

SELECT 2

INSERT 0 3

SELECT * FROM products_ext;

product_id
key
value

test1
foo
"Foo"

test1
bar
"Bar"

test2
foo
"Foo"

SELECT 3

SELECT
  p.product_id AS product_id,
  json_object_agg(e.key, e.value)
FROM
  products AS p
JOIN
  products_ext AS e ON p.product_id = e.product_id
GROUP BY p.product_id;

product_id
json_object_agg

test1
{ "foo" : "Foo", "bar" : "Bar" }

test2
{ "foo" : "Foo" }

SELECT 2