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.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

DROP SCHEMA IF EXISTS "test" CASCADE;
CREATE SCHEMA "test";

DROP TABLE IF EXISTS "test"."source";
CREATE TABLE "test"."source" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "title" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::text,
  PRIMARY KEY ("id")
);

INSERT INTO "test"."source" ("id", "title") VALUES ('4536262a-d4a4-4843-946c-59e8cc680687', '来源可爱');
INSERT INTO "test"."source" ("id", "title") VALUES ('5e593bfa-a9b1-4206-bbf0-75745aab5472', '来源猫咪');

DROP TABLE IF EXISTS "test"."source_i18n";
CREATE TABLE "test"."source_i18n" (
  "id" bigserial NOT NULL,
  "source_id" uuid NOT NULL,
  "title" text COLLATE "pg_catalog"."default",
  "language_code" text COLLATE "pg_catalog"."default" NOT NULL,
  PRIMARY KEY ("id")
);

INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (1, '4536262a-d4a4-4843-946c-59e8cc680687', 'Sourced Cute', 'en-US');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (2, '4536262a-d4a4-4843-946c-59e8cc680687', '来源可爱', 'zh-CN');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (3, '4536262a-d4a4-4843-946c-59e8cc680687', '來源可愛', 'zh-TW');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (4, '4536262a-d4a4-4843-946c-59e8cc680687', '來源可愛', 'zh-HK');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (5, '4536262a-d4a4-4843-946c-59e8cc680687', 'ラブリーから', 'jp-JP');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (6, '5e593bfa-a9b1-4206-bbf0-75745aab5472', 'Sourced Cat', 'en-US');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (7, '5e593bfa-a9b1-4206-bbf0-75745aab5472', '来源猫咪', 'zh-CN');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (8, '5e593bfa-a9b1-4206-bbf0-75745aab5472', '來源貓咪', 'zh-TW');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (9, '5e593bfa-a9b1-4206-bbf0-75745aab5472', '來源貓咪', 'zh-HK');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (10, '5e593bfa-a9b1-4206-bbf0-75745aab5472', 'ソースキャッツ', 'jp-JP');

DROP SCHEMA

CREATE SCHEMA

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

SELECT
  "source".*,
  i18n.items as i18n
FROM "test"."source" AS "source"
LEFT JOIN
  (
  SELECT
    "source_id",
    JSONB_BUILD_OBJECT(
      'title', JSONB_OBJECT_AGG(language_code, title)
    ) AS items
    FROM "test"."source_i18n"
    GROUP BY "source_id"
  ) AS "i18n"
ON "i18n"."source_id" = "source"."id";

id
title
i18n

4536262a-d4a4-4843-946c-59e8cc680687
来源可爱
{"title": {"en-US": "Sourced Cute", "jp-JP": "ラブリーから", "zh-CN": "来源可爱", "zh-HK": "來源可愛", "zh-TW": "來源可愛"}}

5e593bfa-a9b1-4206-bbf0-75745aab5472
来源猫咪
{"title": {"en-US": "Sourced Cat", "jp-JP": "ソースキャッツ", "zh-CN": "来源猫咪", "zh-HK": "來源貓咪", "zh-TW": "來源貓咪"}}

SELECT 2

id	title	i18n
4536262a-d4a4-4843-946c-59e8cc680687	来源可爱	{"title": {"en-US": "Sourced Cute", "jp-JP": "ラブリーから", "zh-CN": "来源可爱", "zh-HK": "來源可愛", "zh-TW": "來源可愛"}}
5e593bfa-a9b1-4206-bbf0-75745aab5472	来源猫咪	{"title": {"en-US": "Sourced Cat", "jp-JP": "ソースキャッツ", "zh-CN": "来源猫咪", "zh-HK": "來源貓咪", "zh-TW": "來源貓咪"}}