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 book
(
    pk_book_id integer NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    isbn character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT book_pkey PRIMARY KEY (pk_book_id)
);

CREATE TABLE


CREATE TABLE author
(
    pk_author_id integer NOT NULL,
    fullname character varying(255) COLLATE pg_catalog."default",
    mobileno character(10) COLLATE pg_catalog."default",
    fk_book_id integer,
    CONSTRAINT author_pkey PRIMARY KEY (pk_author_id),
    CONSTRAINT author_fk_book_id_fkey FOREIGN KEY (fk_book_id)
        REFERENCES book (pk_book_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

CREATE TABLE


INSERT INTO book(
    pk_book_id, name, isbn)
    VALUES (1, 'Hot Deals', 'ISBN89644'),
    (2, 'Summer Tales', 'ISBN405987');

INSERT 0 2


INSERT INTO author(
    pk_author_id, fullname, mobileno, fk_book_id)
values    
    (100, 'John Matthew', '98927828', 1),
    (101, 'Mark Knight', '99875528', 1),
    (103, 'Lara Croft', '99872628', 2),
    (104, 'Brad Show', '567982', 2);

INSERT 0 4


select to_jsonb(b), authors
from book b
  join (
     select fk_book_id, jsonb_agg(to_jsonb(a)) authors
     from author a
     group by fk_book_id
  ) a on a.fk_book_id = b.pk_book_id;


to_jsonb
authors


{"isbn": "ISBN405987", "name": "Summer Tales", "pk_book_id": 2}
[{"fullname": "Lara Croft", "mobileno": "99872628  ", "fk_book_id": 2, "pk_author_id": 103}, {"fullname": "Brad Show", "mobileno": "567982    ", "fk_book_id": 2, "pk_author_id": 104}]

{"isbn": "ISBN89644", "name": "Hot Deals", "pk_book_id": 1}
[{"fullname": "John Matthew", "mobileno": "98927828  ", "fk_book_id": 1, "pk_author_id": 100}, {"fullname": "Mark Knight", "mobileno": "99875528  ", "fk_book_id": 1, "pk_author_id": 101}]



SELECT 2

to_jsonb	authors
{"isbn": "ISBN405987", "name": "Summer Tales", "pk_book_id": 2}	[{"fullname": "Lara Croft", "mobileno": "99872628 ", "fk_book_id": 2, "pk_author_id": 103}, {"fullname": "Brad Show", "mobileno": "567982 ", "fk_book_id": 2, "pk_author_id": 104}]
{"isbn": "ISBN89644", "name": "Hot Deals", "pk_book_id": 1}	[{"fullname": "John Matthew", "mobileno": "98927828 ", "fk_book_id": 1, "pk_author_id": 100}, {"fullname": "Mark Knight", "mobileno": "99875528 ", "fk_book_id": 1, "pk_author_id": 101}]