Alternate approach suggested by Mike Rylander: build on the materialized summary table, which will have way fewer rows, and also not require gnarly SQL. So for example:
CREATE OR REPLACE VIEW money.transaction_billing_summary AS SELECT id AS xact, last_billing_type, last_billing_note, last_billing_ts, total_owed FROM money.materialized_billable_xact_summary;
Performance:
EXPLAIN ANALYZE SELECT * FROM money.transaction_billing_summary WHERE xact = 1055743; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using materialized_billable_xact_summary_pkey on materialized_billable_xact_summary (cost=0.00..6.45 rows=1 width=72) (actual time=0.070..0.070 rows=1 loops=1)
Index Cond: (id = 1055743)
Total runtime: 0.094 ms
(3 rows)
Alternate approach suggested by Mike Rylander: build on the materialized summary table, which will have way fewer rows, and also not require gnarly SQL. So for example:
CREATE OR REPLACE VIEW money.transacti on_billing_ summary AS SELECT id AS xact, last_billing_type, last_billing_note, last_billing_ts, total_owed FROM money.materiali zed_billable_ xact_summary;
Performance:
EXPLAIN ANALYZE SELECT * FROM money.transacti on_billing_ summary WHERE xact = 1055743;
QUERY PLAN ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ----- billable_ xact_summary_ pkey on materialized_ billable_ xact_summary (cost=0.00..6.45 rows=1 width=72) (actual time=0.070..0.070 rows=1 loops=1)
-------
Index Scan using materialized_
Index Cond: (id = 1055743)
Total runtime: 0.094 ms
(3 rows)
Seems good!