explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sK2O

Settings
# exclusive inclusive rows x rows loops node
1. 1,233.895 540,441.127 ↑ 2.5 113,296 1

Sort (cost=2,937,192.53..2,937,906.25 rows=285,487 width=538) (actual time=540,371.879..540,441.127 rows=113,296 loops=1)

  • Sort Key: balancelist_tripica.mba_id, balancelist_tripica.cba_id, balancelist_tripica.charge_creation, balancelist_tripica.aggregate_id, balancelist_tripica.transaction_id
  • Sort Method: external merge Disk: 20304kB
2.          

CTE events

3. 5,955.925 5,955.925 ↓ 1.2 4,709,431 1

Seq Scan on vw_tripica_bookings (cost=0.00..336,980.96 rows=4,054,249 width=252) (actual time=0.391..5,955.925 rows=4,709,431 loops=1)

  • Filter: (((charge_created >= '2019-07-01'::date) AND (charge_created <= '2019-09-01'::date)) OR ((charge_created < '2019-07-01'::date) AND (payment_last_change_status_ts <= '2019-09-01'::date)))
  • Rows Removed by Filter: 421567
4. 117.554 539,207.232 ↑ 2.5 113,296 1

Subquery Scan on balancelist_tripica (cost=2,412,938.12..2,435,777.08 rows=285,487 width=538) (actual time=534,141.779..539,207.232 rows=113,296 loops=1)

5. 3,836.352 539,089.678 ↑ 2.5 113,296 1

Unique (cost=2,412,938.12..2,428,639.90 rows=285,487 width=534) (actual time=534,141.761..539,089.678 rows=113,296 loops=1)

6.          

CTE end_amounts

7. 6,684.140 147,249.481 ↑ 2.9 13,744 1

GroupAggregate (cost=1,191,213.15..1,718,965.52 rows=40,000 width=96) (actual time=138,484.502..147,249.481 rows=13,744 loops=1)

  • Group Key: tb.mba_id, tb.cba_id
8. 59,318.571 140,565.341 ↓ 1.2 4,709,431 1

Sort (cost=1,191,213.15..1,201,348.77 rows=4,054,249 width=164) (actual time=138,484.106..140,565.341 rows=4,709,431 loops=1)

  • Sort Key: tb.mba_id, tb.cba_id
  • Sort Method: external merge Disk: 348616kB
9. 81,246.770 81,246.770 ↓ 1.2 4,709,431 1

CTE Scan on events tb (cost=0.00..81,084.98 rows=4,054,249 width=164) (actual time=0.395..81,246.770 rows=4,709,431 loops=1)

10. 180,803.132 535,253.326 ↓ 16.4 4,673,166 1

Sort (cost=693,972.60..694,686.32 rows=285,487 width=534) (actual time=534,141.759..535,253.326 rows=4,673,166 loops=1)

  • Sort Key: e.ouid, e.mba_id, e.cba_id, e.first_name, e.given_name, (CASE WHEN ((e.mpid)::text = ANY ('{9870075400001,9800181200003,9900269000000,9906214000003}'::text[])) THEN 'iA'::text ELSE 'ooA'::text END), e.charge_created, e.event_type, e.analyticaxis, (CASE WHEN e.is_abschlag_cancelled THEN abs(e.tax_included_euro_amount) WHEN (e.is_abschlag AND ((e.payment_status)::text = 'REJECTED'::text)) THEN abs(e.tax_included_euro_amount) WHEN (e.is_abschlag AND ((e.payment_last_change_status_ts IS NOT NULL) OR (e.payment_last_change_status_ts <= '2019-09-01'::date))) THEN NULL::numeric WHEN (e.is_abschlag AND ((e.payment_last_change_status_ts IS NULL) OR (e.payment_last_change_status_ts >= '2019-09-01'::date))) THEN abs(e.tax_included_euro_amount) WHEN (e.is_bill_cancel AND (e.charge_created <= '2019-07-01'::date) AND (e.payment_last_change_status_ts >= '2019-07-01'::date) AND (e.payment_last_change_status_ts <= '2019-09-01'::date) AND (e.tax_included_euro_amount > '0'::numeric)) THEN abs(e.tax_included_euro_amount) WHEN (e.is_bill_cancel AND (e.charge_created >= '2019-07-01'::date) AND (e.payment_last_change_status_ts >= '2019-07-01'::date) AND (e.payment_last_change_status_ts <= '2019-09-01'::date)) THEN abs(e.tax_included_euro_amount) WHEN (e.is_bill_cancel AND (e.charge_created >= '2019-07-01'::date) AND ((e.payment_last_change_status_ts IS NULL) OR (e.payment_last_change_status_ts >= '2019-09-01'::date)) AND (e.tax_included_euro_amount < '0'::numeric)) THEN abs(e.tax_included_euro_amount) WHEN e.is_bill_cancel THEN '0'::numeric WHEN ((e.tax_included_euro_amount >= '0'::numeric) AND ((e.event_type)::text ~~ '%_AUSG'::text)) THEN ('-1'::numeric * abs(e.tax_included_euro_amount)) WHEN (e.tax_included_euro_amount >= '0'::numeric) THEN abs(e.tax_included_euro_amount) ELSE '0'::numeric END), (CASE WHEN e.is_abschlag_cancelled THEN NULL::numeric WHEN (e.is_bill_cancel AND (e.charge_created <= '2019-07-01'::date) AND (e.payment_last_change_status_ts >= '2019-07-01'::date) AND (e.payment_last_change_status_ts <= '2019-09-01'::date) AND (e.tax_included_euro_amount < '0'::numeric)) THEN abs(e.tax_included_euro_amount) WHEN (e.is_bill_cancel AND (e.charge_created >= '2019-07-01'::date) AND (e.payment_last_change_status_ts >= '2019-07-01'::date) AND (e.payment_last_change_status_ts <= '2019-09-01'::date)) THEN abs(e.tax_included_euro_amount) WHEN (e.is_bill_cancel AND (e.charge_created >= '2019-07-01'::date) AND ((e.payment_last_change_status_ts IS NULL) OR (e.payment_last_change_status_ts >= '2019-09-01'::date)) AND (e.tax_included_euro_amount > '0'::numeric)) THEN abs(e.tax_included_euro_amount) WHEN e.is_bill_cancel THEN '0'::numeric WHEN (((e.event_type)::text = 'BANK_FEE'::text) AND (e.payment_last_change_status_ts <= '2019-09-01'::date) AND ((e.payment_status)::text = 'DONE'::text)) THEN abs(e.tax_included_euro_amount) WHEN (e.is_abschlag AND ((e.payment_last_change_status_ts IS NULL) OR (e.payment_last_change_status_ts >= '2019-09-01'::date) OR ((e.payment_status)::text = 'REJECTED'::text))) THEN NULL::numeric WHEN (e.is_abschlag AND (e.payment_last_change_status_ts <= '2019-09-01'::date)) THEN abs(e.tax_included_euro_amount) WHEN ((e.tax_included_euro_amount < '0'::numeric) AND ((e.event_type)::text ~~ '%_AUSG'::text)) THEN ('-1'::numeric * abs(e.tax_included_euro_amount)) WHEN (e.tax_included_euro_amount < '0'::numeric) THEN abs(e.tax_included_euro_amount) ELSE '0'::numeric END), (CASE WHEN (end_amounts.amount_sum IS NULL) THEN '0'::numeric ELSE end_amounts.amount_sum END), (CASE WHEN (e.is_abschlag AND ((e.payment_last_change_status_ts IS NULL) OR (e.payment_last_change_status_ts < '2019-07-01'::date) OR (e.payment_last_change_status_ts > '2019-09-01'::date))) THEN (e.bill_due_date)::timestamp without time zone ELSE e.booking_date END), e.bill_cancelled, e.tax_included_euro_amount, e.booking_date, e.ledger_account_nr, e.bill_due_date, e.payment_last_change_status_ts, e.aggregate_id, e.transaction_id
  • Sort Method: external merge Disk: 974960kB
11. 6,674.436 354,450.194 ↓ 16.4 4,673,166 1

Merge Right Join (cost=484,799.47..531,491.10 rows=285,487 width=534) (actual time=343,809.225..354,450.194 rows=4,673,166 loops=1)

  • Merge Cond: (((end_amounts.mba_id)::text = (e.mba_id)::text) AND ((end_amounts.cba_id)::text = (e.cba_id)::text))
12. 15.560 147,283.601 ↑ 2.9 13,744 1

Sort (cost=5,908.54..6,008.54 rows=40,000 width=96) (actual time=147,279.550..147,283.601 rows=13,744 loops=1)

  • Sort Key: end_amounts.mba_id, end_amounts.cba_id
  • Sort Method: quicksort Memory: 1458kB
13. 147,268.041 147,268.041 ↑ 2.9 13,744 1

CTE Scan on end_amounts (cost=0.00..800.00 rows=40,000 width=96) (actual time=138,484.506..147,268.041 rows=13,744 loops=1)

14. 826.954 200,492.157 ↓ 16.4 4,673,166 1

Materialize (cost=478,890.92..480,318.36 rows=285,487 width=429) (actual time=196,529.629..200,492.157 rows=4,673,166 loops=1)

15. 186,483.034 199,665.203 ↓ 16.4 4,673,166 1

Sort (cost=478,890.92..479,604.64 rows=285,487 width=429) (actual time=196,529.623..199,665.203 rows=4,673,166 loops=1)

  • Sort Key: e.mba_id, e.cba_id
  • Sort Method: external merge Disk: 915224kB
16. 949.520 13,182.169 ↓ 16.4 4,673,166 1

Subquery Scan on e (cost=0.00..341,777.42 rows=285,487 width=429) (actual time=0.965..13,182.169 rows=4,673,166 loops=1)

17. 477.085 12,232.649 ↓ 16.4 4,673,166 1

Append (cost=0.00..338,922.55 rows=285,487 width=641) (actual time=0.963..12,232.649 rows=4,673,166 loops=1)

18. 5,315.085 5,315.085 ↓ 17.7 4,673,166 1

CTE Scan on events (cost=0.00..146,966.53 rows=263,526 width=641) (actual time=0.960..5,315.085 rows=4,673,166 loops=1)

  • Filter: ((event_type)::text = ANY ('{BANK_FEE_RETURN,BANK_FEE,BANK_FEE_REJECTED,BANK_FEE_RECEIVABLE,DUNNING_FEE_RECEIVABLE,ELECTRICITY_ABSCHLAG,GAS_ABSCHLAG,ELECTRICITY_ABSCHLAG_CANCEL,GAS_ABSCHLAG_CANCEL,ELECTRICITY_ABSCHLAG_REJECTED,GAS_ABSCHLAG_REJECTED,ELECTRICITY_ABSCHLAG_CANCEL,GAS_ABSCHLAG_CANCEL}'::text[]))
  • Rows Removed by Filter: 36265
19. 0.001 6,440.479 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..187,893.33 rows=21,961 width=641) (actual time=6,440.479..6,440.479 rows=0 loops=1)

20. 6,440.478 6,440.478 ↓ 0.0 0 1

CTE Scan on events e_1 (cost=0.00..187,673.72 rows=21,961 width=641) (actual time=6,440.478..6,440.478 rows=0 loops=1)

  • Filter: ((is_abschlag OR is_abschlag_rejected) AND (charge_created < '2019-07-01'::date) AND (payment_last_change_status_ts <= '2019-09-01'::date) AND ((CASE WHEN is_abschlag THEN (concat(event_type, '_AUSG'))::character varying WHEN is_abschlag_rejected THEN (concat(event_type, '_AUSG'))::character varying ELSE event_type END)::text = ANY ('{BANK_FEE_RETURN,BANK_FEE,BANK_FEE_REJECTED,BANK_FEE_RECEIVABLE,DUNNING_FEE_RECEIVABLE,ELECTRICITY_ABSCHLAG,GAS_ABSCHLAG,ELECTRICITY_ABSCHLAG_CANCEL,GAS_ABSCHLAG_CANCEL,ELECTRICITY_ABSCHLAG_REJECTED,GAS_ABSCHLAG_REJECTED,ELECTRICITY_ABSCHLAG_CANCEL,GAS_ABSCHLAG_CANCEL}'::text[])))
  • Rows Removed by Filter: 4709431
Planning time : 111.672 ms
Execution time : 540,957.810 ms