explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m9E1

Settings
# exclusive inclusive rows x rows loops node
1. 0.372 62,310.994 ↑ 631.7 264 1

Sort (cost=5,071,014.53..5,071,431.46 rows=166,769 width=66) (actual time=62,310.949..62,310.994 rows=264 loops=1)

  • Output: ae.money, ae.money_currency, ae.money_in_global, ae.money_in_global_currency, s.currency, d.currency, mte.event_type, ae.account_fee, ae.account_transaction_id, ae.account_id, ah.account_holder_id, ae.date_posted
  • Sort Key: ae.date_posted DESC
  • Sort Method: quicksort Memory: 62kB
2. 1,709.374 62,310.622 ↑ 631.7 264 1

Hash Semi Join (cost=2,656,190.75..5,056,549.42 rows=166,769 width=66) (actual time=35,437.965..62,310.622 rows=264 loops=1)

  • Output: ae.money, ae.money_currency, ae.money_in_global, ae.money_in_global_currency, s.currency, d.currency, mte.event_type, ae.account_fee, ae.account_transaction_id, ae.account_id, ah.account_holder_id, ae.date_posted
  • Hash Cond: (ae.account_transaction_id = ae1.account_transaction_id)
3. 5,313.369 60,599.876 ↓ 1.0 21,398,364 1

Hash Left Join (cost=2,171,051.69..4,494,282.32 rows=21,210,428 width=66) (actual time=34,241.346..60,599.876 rows=21,398,364 loops=1)

  • Output: ae.money, ae.money_currency, ae.money_in_global, ae.money_in_global_currency, ae.account_fee, ae.account_transaction_id, ae.account_id, ae.date_posted, mte.event_type, s.currency, d.currency, ah.account_holder_id
  • Hash Cond: (ae.account_id = a2.account_id)
4. 11,597.030 55,261.381 ↓ 1.0 21,398,364 1

Hash Left Join (cost=2,162,005.24..4,193,592.49 rows=21,210,428 width=58) (actual time=34,215.978..55,261.381 rows=21,398,364 loops=1)

  • Output: ae.money, ae.money_currency, ae.money_in_global, ae.money_in_global_currency, ae.account_fee, ae.account_transaction_id, ae.account_id, ae.date_posted, mte.event_type, s.currency, d.currency
  • Hash Cond: (ae.money_transfer_event_id = mte.money_transfer_event_id)
  • Filter: (((mte.auditable_event_state_type_id = ANY ('{2,12,13,16}'::integer[])) OR (mte.auditable_event_state_type_id IS NULL)) AND ((mte.event_type <> 5) OR (mte.event_type IS NULL) OR ((mte.event_type = 5) AND (a1.account_holder_id <> 12))))
  • Rows Removed by Filter: 174044
5. 9,518.101 9,518.101 ↓ 1.0 21,572,408 1

Seq Scan on pegasus.account_entry ae (cost=0.00..1,112,142.18 rows=21,549,455 width=54) (actual time=0.035..9,518.101 rows=21,572,408 loops=1)

  • Output: ae.money, ae.money_currency, ae.money_in_global, ae.money_in_global_currency, ae.account_fee, ae.account_transaction_id, ae.account_id, ae.date_posted, ae.money_transfer_event_id
  • Filter: ((ae.date_posted >= '2018-01-09 13:46:10.107'::timestamp without time zone) AND (ae.date_posted <= '2019-01-09 13:46:10.108'::timestamp without time zone))
  • Rows Removed by Filter: 8741779
6. 1,889.707 34,146.250 ↓ 1.0 6,651,565 1

Hash (cost=2,033,393.28..2,033,393.28 rows=6,651,437 width=32) (actual time=34,146.250..34,146.250 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.auditable_event_state_type_id, s.currency, d.currency, a1.account_holder_id
  • Buckets: 8388608 Batches: 2 Memory Usage: 299353kB
7. 1,369.426 32,256.543 ↓ 1.0 6,651,565 1

Hash Left Join (cost=1,195,540.22..2,033,393.28 rows=6,651,437 width=32) (actual time=21,072.588..32,256.543 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.auditable_event_state_type_id, s.currency, d.currency, a1.account_holder_id
  • Hash Cond: (d.account_id = a1.account_id)
8. 7,203.690 30,857.390 ↓ 1.0 6,651,565 1

Hash Right Join (cost=1,187,174.64..1,933,820.98 rows=6,651,437 width=32) (actual time=21,042.604..30,857.390 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.auditable_event_state_type_id, s.currency, d.currency, d.account_id
  • Hash Cond: (d.monetary_endpoint_id = mte.source_id)
9. 2,680.045 2,680.045 ↓ 1.0 13,305,460 1

Seq Scan on pegasus.monetary_endpoint d (cost=0.00..329,445.81 rows=13,288,281 width=20) (actual time=0.012..2,680.045 rows=13,305,460 loops=1)

  • Output: d.currency, d.monetary_endpoint_id, d.account_id
10. 2,187.761 20,973.655 ↓ 1.0 6,651,565 1

Hash (cost=1,058,562.67..1,058,562.67 rows=6,651,437 width=28) (actual time=20,973.655..20,973.655 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.source_id, mte.auditable_event_state_type_id, s.currency
  • Buckets: 8388608 Batches: 2 Memory Usage: 273435kB
11. 9,067.691 18,785.894 ↓ 1.0 6,651,565 1

Hash Right Join (cost=337,870.33..1,058,562.67 rows=6,651,437 width=28) (actual time=6,003.665..18,785.894 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.source_id, mte.auditable_event_state_type_id, s.currency
  • Hash Cond: (s.monetary_endpoint_id = mte.destination_id)
12. 3,782.512 3,782.512 ↓ 1.0 13,305,460 1

Seq Scan on pegasus.monetary_endpoint s (cost=0.00..329,445.81 rows=13,288,281 width=12) (actual time=0.011..3,782.512 rows=13,305,460 loops=1)

  • Output: s.currency, s.monetary_endpoint_id
13. 2,791.765 5,935.691 ↓ 1.0 6,651,565 1

Hash (cost=209,258.37..209,258.37 rows=6,651,437 width=32) (actual time=5,935.691..5,935.691 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.destination_id, mte.source_id, mte.auditable_event_state_type_id
  • Buckets: 8388608 Batches: 2 Memory Usage: 286429kB
14. 3,143.926 3,143.926 ↓ 1.0 6,651,565 1

Seq Scan on pegasus.money_transfer_auditable_event mte (cost=0.00..209,258.37 rows=6,651,437 width=32) (actual time=0.058..3,143.926 rows=6,651,565 loops=1)

  • Output: mte.event_type, mte.money_transfer_event_id, mte.destination_id, mte.source_id, mte.auditable_event_state_type_id
15. 6.194 29.727 ↑ 1.0 36,426 1

Hash (cost=7,910.26..7,910.26 rows=36,426 width=16) (actual time=29.727..29.727 rows=36,426 loops=1)

  • Output: a1.account_id, a1.account_holder_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2220kB
16. 23.533 23.533 ↑ 1.0 36,426 1

Seq Scan on pegasus.account a1 (cost=0.00..7,910.26 rows=36,426 width=16) (actual time=0.013..23.533 rows=36,426 loops=1)

  • Output: a1.account_id, a1.account_holder_id
17. 5.184 25.126 ↑ 1.0 36,426 1

Hash (cost=8,591.12..8,591.12 rows=36,426 width=16) (actual time=25.126..25.126 rows=36,426 loops=1)

  • Output: a2.account_id, ah.account_holder_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2220kB
18. 9.714 19.942 ↑ 1.0 36,426 1

Hash Left Join (cost=180.00..8,591.12 rows=36,426 width=16) (actual time=1.071..19.942 rows=36,426 loops=1)

  • Output: a2.account_id, ah.account_holder_id
  • Hash Cond: (a2.account_holder_id = ah.account_holder_id)
19. 9.193 9.193 ↑ 1.0 36,426 1

Seq Scan on pegasus.account a2 (cost=0.00..7,910.26 rows=36,426 width=16) (actual time=0.014..9.193 rows=36,426 loops=1)

  • Output: a2.account_id, a2.currency, a2.parent_summary_account_id, a2.account_description, a2.account_holder_id, a2.account_number, a2.balance_in_display, a2.balance_in_display_currency, a2.working_balance_in_display, a2.working_balance_in_display_currency, a2.disabled, a2.version, a2.account_type_id, a2.notification_threshold_amount, a2.created_on, a2.account_category_type_id
20. 0.364 1.035 ↓ 1.0 2,890 1

Hash (cost=143.89..143.89 rows=2,889 width=8) (actual time=1.035..1.035 rows=2,890 loops=1)

  • Output: ah.account_holder_id
  • Buckets: 4096 Batches: 1 Memory Usage: 145kB
21. 0.671 0.671 ↓ 1.0 2,890 1

Seq Scan on pegasus.account_holder ah (cost=0.00..143.89 rows=2,889 width=8) (actual time=0.012..0.671 rows=2,890 loops=1)

  • Output: ah.account_holder_id
22. 0.053 1.372 ↑ 7.1 118 1

Hash (cost=485,128.66..485,128.66 rows=832 width=8) (actual time=1.372..1.372 rows=118 loops=1)

  • Output: ae1.account_transaction_id
  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
23. 0.085 1.319 ↑ 7.1 118 1

Nested Loop (cost=9,778.99..485,128.66 rows=832 width=8) (actual time=0.199..1.319 rows=118 loops=1)

  • Output: ae1.account_transaction_id
24. 0.122 0.122 ↓ 4.0 4 1

Index Scan using account_number_account_holder_id_unique on pegasus.account a3 (cost=0.41..8.43 rows=1 width=8) (actual time=0.072..0.122 rows=4 loops=1)

  • Output: a3.account_id, a3.currency, a3.parent_summary_account_id, a3.account_description, a3.account_holder_id, a3.account_number, a3.balance_in_display, a3.balance_in_display_currency, a3.working_balance_in_display, a3.working_balance_in_display_currency, a3.disabled, a3.version, a3.account_type_id, a3.notification_threshold_amount, a3.created_on, a3.account_category_type_id
  • Index Cond: (a3.account_holder_id = 128652)
25. 0.920 1.112 ↑ 7,888.2 30 4

Bitmap Heap Scan on pegasus.account_entry ae1 (cost=9,778.58..482,753.75 rows=236,647 width=16) (actual time=0.062..0.278 rows=30 loops=4)

  • Output: ae1.account_entry_id, ae1.account_id, ae1.account_transaction_id, ae1.money_in_global, ae1.money, ae1.money_in_global_currency, ae1.money_currency, ae1.description, ae1.date_posted, ae1.event_name, ae1.account_fee, ae1.running_balance, ae1.money_transfer_event_id, ae1.fee_event_id, ae1.date_inserted, ae1.money_converted, ae1.money_converted_currency
  • Recheck Cond: (ae1.account_id = a3.account_id)
  • Heap Blocks: exact=68
26. 0.192 0.192 ↑ 7,888.2 30 4

Bitmap Index Scan on account_entry_index (cost=0.00..9,719.42 rows=236,647 width=0) (actual time=0.048..0.048 rows=30 loops=4)

  • Index Cond: (ae1.account_id = a3.account_id)