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 transactions (
   transaction_id varchar(255) NOT NULL,
   amount float8 NOT NULL,
   stock_ticker varchar(255) NOT NULL,
   transaction_type varchar(255) NOT NULL,
   customer_id varchar NOT NULL,
   inserted_at timestamp NOT NULL,
   created_at timestamp NOT NULL,
   CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
);

INSERT INTO transactions(transaction_id, amount, stock_ticker, transaction_type, customer_id, inserted_at, created_at)
VALUES
  ('123123abmk12', 10, 'tsla', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00')
, ('123123abmk13', 20, 'tsla', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00')
, ('123123abmk14',  5, 'goog', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00')
, ('123123abmk15',  8, 'goog', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00')
, ('123123abmk16', 10, 'tsla', 'purchase', 'bbbbbb', '2020-04-01 01:00:00', '2020-04-05 01:00:00')
, ('123123abmk17', 20, 'tsla', 'purchase', 'bbbbbb', '2020-04-03 01:00:00', '2020-04-08 01:00:00')
, ('123123abmk18',  5, 'goog', 'purchase', 'bbbbbb', '2020-04-01 01:00:00', '2020-04-06 01:00:00')
, ('123123abmk19',  8, 'goog', 'purchase', 'bbbbbb', '2020-04-03 01:00:00', '2020-04-07 01:00:00');

CREATE INDEX ix_transactions_customer_id ON transactions USING btree (customer_id);

CREATE TABLE customers (
customer_id varchar(255) PRIMARY KEY,
first_transaction_at timestamp  -- can be NULL!
);

INSERT INTO customers(customer_id, first_transaction_at)
VALUES
  ('a1b2c3', '2020-04-01 01:00:00')
, ('bbbbbb', '2020-04-05 01:00:00')
, ('XXX'   , NULL);

8 rows affected

3 rows affected

customer_id	the_day	tsla_running_amount	goog_running_amount
a1b2c3	2020-04-01	10	5
a1b2c3	2020-04-02	10	5
a1b2c3	2020-04-03	30	13
a1b2c3	2020-04-04	30	13
a1b2c3	2020-04-05	30	13
a1b2c3	2020-04-06	30	13
a1b2c3	2020-04-07	30	13
a1b2c3	2020-04-08	30	13
a1b2c3	2020-04-09	30	13
bbbbbb	2020-04-05	10	null
bbbbbb	2020-04-06	10	5
bbbbbb	2020-04-07	10	13
bbbbbb	2020-04-08	30	13
bbbbbb	2020-04-09	30	13