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?.

CREATE TABLE IF NOT EXISTS cusips (
  name TEXT,
  cusip TEXT,
  ticker TEXT
);

CREATE TABLE IF NOT EXISTS companies (
  name TEXT,
  data JSONB
);

CREATE TABLE



CREATE TABLE


INSERT INTO cusips (name, cusip, ticker) VALUES
  ('Berkshire', '90210', 'BRKA'),
  ('Apple', '90211', 'AAPL'),
  ('Microsoft', '90212', 'MSFT');

INSERT INTO companies (name, data) VALUES
  ('Berkshire', '{"tickers": ["BRKA", "BRKB"]}'),
  ('Apple', '{"tickers": ["AAPL"]}'),
  ('Microsoft', '{"tickers": ["MSFT"]}');

INSERT 0 3



INSERT 0 3


SELECT c.name, c.cusip, c.ticker, jsonb_build_object('ticker', jsonb_agg(cc.ticker))
FROM cusips c CROSS JOIN LATERAL
     (SELECT jsonb_array_elements(co.data->'tickers') AS ticker
      FROM companies co
      WHERE co.name = c.name
     ) cc
WHERE c.cusip = '90210'
GROUP BY c.name, c.cusip, c.ticker


name
cusip
ticker
jsonb_build_object


Berkshire
90210
BRKA
{"ticker": ["BRKA", "BRKB"]}



SELECT 1