explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7m6b

Settings
# exclusive inclusive rows x rows loops node
1. 0.139 512,843.861 ↑ 3.5 130 1

Sort (cost=8,330,801.27..8,330,802.40 rows=455 width=734) (actual time=512,843.856..512,843.861 rows=130 loops=1)

  • Output: orig.when_occurred, rev.when_occurred, ot.reference, orig.trace_id, (round(rev.money, 2)), rev.money_currency, (round(fe.money, 2)), fe.money_currency, et.event_type_description, rt.reference, sa.account_number, da.account_number, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference, sc.company_name, orig.description, rev.description, ('REVERSAL'::text)
  • Sort Key: orig.when_occurred
  • Sort Method: quicksort Memory: 59kB
2. 0.230 512,843.722 ↑ 3.5 130 1

HashAggregate (cost=8,330,776.63..8,330,781.18 rows=455 width=734) (actual time=512,843.668..512,843.722 rows=130 loops=1)

  • Output: orig.when_occurred, rev.when_occurred, ot.reference, orig.trace_id, (round(rev.money, 2)), rev.money_currency, (round(fe.money, 2)), fe.money_currency, et.event_type_description, rt.reference, sa.account_number, da.account_number, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference, sc.company_name, orig.description, rev.description, ('REVERSAL'::text)
  • Group Key: orig.when_occurred, rev.when_occurred, ot.reference, orig.trace_id, (round(rev.money, 2)), rev.money_currency, (round(fe.money, 2)), fe.money_currency, et.event_type_description, rt.reference, sa.account_number, da.account_number, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference, sc.company_name, orig.description, rev.description, ('REVERSAL'::text)
3. 0.015 512,843.492 ↑ 3.5 130 1

Append (cost=6,401,583.52..8,330,749.33 rows=455 width=734) (actual time=512,843.224..512,843.492 rows=130 loops=1)

4. 0.002 81.815 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,401,583.52..6,490,562.56 rows=5 width=499) (actual time=81.814..81.815 rows=0 loops=1)

  • Output: orig.when_occurred, rev.when_occurred, ot.reference, orig.trace_id, round(rev.money, 2), rev.money_currency, round(fe.money, 2), fe.money_currency, et.event_type_description, rt.reference, sa.account_number, da.account_number, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference, sc.company_name, orig.description, rev.description, 'REVERSAL'::text
  • Inner Unique: true
5. 0.022 81.813 ↓ 0.0 0 1

Merge Join (cost=6,401,583.38..6,490,561.76 rows=5 width=399) (actual time=81.813..81.813 rows=0 loops=1)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, da.account_number, sc.company_name, ot.reference, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference
  • Inner Unique: true
  • Merge Cond: (sah.company_id = sc.company_id)
6. 1.244 81.791 ↓ 0.0 0 1

Nested Loop (cost=6,401,581.26..9,337,866.53 rows=198 width=387) (actual time=81.791..81.791 rows=0 loops=1)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, da.account_number, sah.company_id, ot.reference, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference
  • Join Filter: (sa.account_holder_id = sah.account_holder_id)
7. 39.359 39.359 ↑ 1.0 2,942 1

Index Scan using fki_client_id_fk on pegasus.account_holder sah (cost=0.28..614.35 rows=2,942 width=16) (actual time=0.016..39.359 rows=2,942 loops=1)

  • Output: sah.account_holder_id, sah.parent_id, sah.created_on, sah.last_updated, sah.company_id, sah.fees_currency, sah.email_address, sah.account_holder_type_id, sah.owner_user_id, sah.account_holder_number, sah.kyc_level_id, sah.default_account_id, sah.default_card_payee_id, sah.default_bank_payee_id, sah.max_number_accounts
8. 0.000 41.188 ↓ 0.0 0 2,942

Materialize (cost=6,401,580.98..9,328,514.94 rows=198 width=387) (actual time=0.014..0.014 rows=0 loops=2,942)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, sa.account_holder_id, da.account_number, ot.reference, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference
9. 0.029 41.482 ↓ 0.0 0 1

Hash Right Join (cost=6,401,580.98..9,328,513.95 rows=198 width=387) (actual time=41.482..41.482 rows=0 loops=1)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, sa.account_holder_id, da.account_number, ot.reference, ae.account_transaction_id, (string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text)), (round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2)), (round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2)), at.reference
  • Hash Cond: (at.reference = ot.reference)
10. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=6,225,462.29..8,711,348.88 rows=32,075,956 width=216) (never executed)

  • Output: ae.account_transaction_id, string_agg(DISTINCT (CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text), string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text), string_agg(DISTINCT (CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money_currency ELSE NULL::bpchar END)::text, ', '::text), round(sum(CASE WHEN ((ae.money > '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2), round(sum(CASE WHEN ((ae.money < '0'::numeric) AND (NOT ae.account_fee)) THEN ae.money ELSE NULL::numeric END), 2), round(sum(CASE WHEN ((ae.money < '0'::numeric) AND ae.account_fee) THEN ae.money ELSE NULL::numeric END), 2), at.reference
  • Group Key: ae.account_transaction_id, at.reference
11. 0.000 0.000 ↓ 0.0 0

Sort (cost=6,225,462.29..6,305,652.18 rows=32,075,956 width=35) (never executed)

  • Output: ae.account_transaction_id, at.reference, ae.money, ae.account_fee, ae.money_currency
  • Sort Key: ae.account_transaction_id, at.reference
12. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=247,727.25..1,349,315.25 rows=32,075,956 width=35) (never executed)

  • Output: ae.account_transaction_id, at.reference, ae.money, ae.account_fee, ae.money_currency
  • Inner Unique: true
  • Hash Cond: (ae.account_transaction_id = at.account_transaction_id)
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on pegasus.account_entry ae (cost=0.00..1,017,388.56 rows=32,075,956 width=19) (never executed)

  • Output: ae.account_entry_id, ae.account_id, ae.account_transaction_id, ae.money_in_global, ae.money, ae.money_in_global_currency, ae.money_currency, ae.description, ae.date_posted, ae.event_name, ae.account_fee, ae.running_balance, ae.money_transfer_event_id, ae.fee_event_id, ae.date_inserted, ae.money_converted, ae.money_converted_currency
14. 0.000 0.000 ↓ 0.0 0

Hash (cost=160,437.11..160,437.11 rows=6,983,211 width=24) (never executed)

  • Output: at.reference, at.account_transaction_id
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on pegasus.account_transaction at (cost=0.00..160,437.11 rows=6,983,211 width=24) (never executed)

  • Output: at.reference, at.account_transaction_id
16. 0.000 41.453 ↓ 0.0 0 1

Hash (cost=176,118.16..176,118.16 rows=43 width=171) (actual time=41.453..41.453 rows=0 loops=1)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, sa.account_holder_id, da.account_number, ot.reference
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 18.365 41.888 ↓ 0.0 0 1

Gather (cost=6,046.89..176,118.16 rows=43 width=171) (actual time=41.452..41.888 rows=0 loops=1)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, sa.account_holder_id, da.account_number, ot.reference
  • Workers Planned: 4
  • Workers Launched: 4
18. 0.001 23.523 ↓ 0.0 0 5

Nested Loop Left Join (cost=5,046.89..175,113.86 rows=11 width=171) (actual time=23.523..23.523 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, sa.account_holder_id, da.account_number, ot.reference
  • Inner Unique: true
  • Worker 0: actual time=17.238..17.238 rows=0 loops=1
  • Worker 1: actual time=21.595..21.595 rows=0 loops=1
  • Worker 2: actual time=21.592..21.592 rows=0 loops=1
  • Worker 3: actual time=21.180..21.180 rows=0 loops=1
19. 0.000 23.522 ↓ 0.0 0 5

Nested Loop Left Join (cost=5,046.46..175,108.73 rows=11 width=163) (actual time=23.522..23.522 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.event_type, rt.reference, sa.account_number, sa.account_holder_id, da.account_number, saeo.account_transaction_id
  • Join Filter: (saeo.account_id = src.account_id)
  • Worker 0: actual time=17.237..17.237 rows=0 loops=1
  • Worker 1: actual time=21.594..21.594 rows=0 loops=1
  • Worker 2: actual time=21.591..21.591 rows=0 loops=1
  • Worker 3: actual time=21.179..21.179 rows=0 loops=1
20. 0.001 23.522 ↓ 0.0 0 5

Nested Loop Left Join (cost=5,045.90..175,093.43 rows=11 width=171) (actual time=23.522..23.522 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, fe.money, fe.money_currency, orig.when_occurred, orig.trace_id, orig.description, orig.money_transfer_event_id, orig.event_type, rt.reference, src.account_id, sa.account_number, sa.account_holder_id, da.account_number
  • Worker 0: actual time=17.236..17.236 rows=0 loops=1
  • Worker 1: actual time=21.594..21.594 rows=0 loops=1
  • Worker 2: actual time=21.590..21.590 rows=0 loops=1
  • Worker 3: actual time=21.178..21.178 rows=0 loops=1
21. 0.001 23.521 ↓ 0.0 0 5

Nested Loop Left Join (cost=5,045.46..175,030.44 rows=7 width=169) (actual time=23.521..23.521 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, orig.when_occurred, orig.trace_id, orig.description, orig.money_transfer_event_id, orig.event_type, rt.reference, src.account_id, sa.account_number, sa.account_holder_id, da.account_number
  • Inner Unique: true
  • Worker 0: actual time=17.236..17.236 rows=0 loops=1
  • Worker 1: actual time=21.593..21.593 rows=0 loops=1
  • Worker 2: actual time=21.589..21.589 rows=0 loops=1
  • Worker 3: actual time=21.177..21.177 rows=0 loops=1
22. 0.001 23.520 ↓ 0.0 0 5

Nested Loop (cost=5,045.17..175,028.27 rows=7 width=161) (actual time=23.520..23.520 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, orig.when_occurred, orig.trace_id, orig.description, orig.money_transfer_event_id, orig.event_type, rt.reference, dst.account_id, src.account_id, sa.account_number, sa.account_holder_id
  • Inner Unique: true
  • Worker 0: actual time=17.235..17.235 rows=0 loops=1
  • Worker 1: actual time=21.592..21.592 rows=0 loops=1
  • Worker 2: actual time=21.589..21.589 rows=0 loops=1
  • Worker 3: actual time=21.177..21.177 rows=0 loops=1
23. 0.001 23.519 ↓ 0.0 0 5

Nested Loop (cost=5,044.88..175,026.10 rows=7 width=137) (actual time=23.519..23.519 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, orig.when_occurred, orig.trace_id, orig.description, orig.money_transfer_event_id, orig.event_type, rt.reference, dst.account_id, src.account_id
  • Inner Unique: true
  • Worker 0: actual time=17.235..17.235 rows=0 loops=1
  • Worker 1: actual time=21.591..21.591 rows=0 loops=1
  • Worker 2: actual time=21.588..21.588 rows=0 loops=1
  • Worker 3: actual time=21.176..21.176 rows=0 loops=1
24. 0.001 23.518 ↓ 0.0 0 5

Nested Loop Left Join (cost=5,044.45..175,021.96 rows=7 width=137) (actual time=23.518..23.518 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, orig.when_occurred, orig.trace_id, orig.description, orig.source_id, orig.money_transfer_event_id, orig.event_type, rt.reference, dst.account_id
  • Inner Unique: true
  • Worker 0: actual time=17.234..17.234 rows=0 loops=1
  • Worker 1: actual time=21.590..21.590 rows=0 loops=1
  • Worker 2: actual time=21.587..21.587 rows=0 loops=1
  • Worker 3: actual time=21.175..21.175 rows=0 loops=1
25. 0.000 23.517 ↓ 0.0 0 5

Nested Loop Left Join (cost=5,044.01..175,017.82 rows=7 width=137) (actual time=23.517..23.517 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, orig.when_occurred, orig.trace_id, orig.description, orig.destination_id, orig.source_id, orig.money_transfer_event_id, orig.event_type, rt.reference
  • Worker 0: actual time=17.233..17.233 rows=0 loops=1
  • Worker 1: actual time=21.589..21.589 rows=0 loops=1
  • Worker 2: actual time=21.586..21.586 rows=0 loops=1
  • Worker 3: actual time=21.174..21.174 rows=0 loops=1
26. 0.354 23.520 ↓ 0.0 0 5

Hash Join (cost=5,043.58..174,958.60 rows=7 width=121) (actual time=23.516..23.520 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, orig.when_occurred, orig.trace_id, orig.description, orig.destination_id, orig.source_id, orig.money_transfer_event_id, orig.event_type
  • Hash Cond: ((orig.trace_id)::text = (rev.trace_id)::text)
  • Join Filter: (orig.money_transfer_event_id <> rev.money_transfer_event_id)
  • Worker 0: actual time=17.232..17.236 rows=0 loops=1
  • Worker 1: actual time=21.588..21.594 rows=0 loops=1
  • Worker 2: actual time=21.585..21.590 rows=0 loops=1
  • Worker 3: actual time=21.173..21.178 rows=0 loops=1
27. 0.031 0.031 ↑ 1,757,231.0 1 5

Parallel Seq Scan on pegasus.money_transfer_auditable_event orig (cost=0.00..163,325.31 rows=1,757,231 width=72) (actual time=0.031..0.031 rows=1 loops=5)

  • Output: orig.money_transfer_event_id, orig.money, orig.money_currency, orig.description, orig.source_id, orig.destination_id, orig.when_occurred, orig.when_noticed, orig.auditable_event_state_type_id, orig.trace_id, orig.event_type, orig.initiator_id, orig.initiator_user_id, orig.auto_release_on, orig.original_trace_id, orig.payment_category_type_id, orig.kyc_id
  • Worker 0: actual time=0.030..0.030 rows=1 loops=1
  • Worker 1: actual time=0.027..0.027 rows=1 loops=1
  • Worker 2: actual time=0.037..0.037 rows=1 loops=1
  • Worker 3: actual time=0.035..0.035 rows=1 loops=1
28. 0.002 23.135 ↓ 0.0 0 5

Hash (cost=5,043.24..5,043.24 rows=27 width=61) (actual time=23.134..23.135 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, rev.trace_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Worker 0: actual time=16.876..16.877 rows=0 loops=1
  • Worker 1: actual time=21.192..21.193 rows=0 loops=1
  • Worker 2: actual time=20.975..20.976 rows=0 loops=1
  • Worker 3: actual time=20.664..20.665 rows=0 loops=1
29. 23.133 23.133 ↓ 0.0 0 5

Index Scan using idx_money_transfer_auditable_event_event_type on pegasus.money_transfer_auditable_event rev (cost=0.43..5,043.24 rows=27 width=61) (actual time=23.133..23.133 rows=0 loops=5)

  • Output: rev.when_occurred, rev.money, rev.money_currency, rev.description, rev.money_transfer_event_id, rev.trace_id
  • Index Cond: (rev.event_type = 26)
  • Filter: ((date(rev.when_occurred) >= '2019-01-01'::date) AND (date(rev.when_occurred) <= '2019-01-31'::date))
  • Rows Removed by Filter: 3882
  • Worker 0: actual time=16.875..16.875 rows=0 loops=1
  • Worker 1: actual time=21.191..21.191 rows=0 loops=1
  • Worker 2: actual time=20.974..20.974 rows=0 loops=1
  • Worker 3: actual time=20.662..20.662 rows=0 loops=1
30. 0.000 0.000 ↓ 0.0 0

Index Scan using fki_transaction_event_id_fkey on pegasus.account_transaction rt (cost=0.43..8.45 rows=1 width=24) (never executed)

  • Output: rt.account_transaction_id, rt.created_on, rt.auditable_event_id, rt.external_reference, rt.processor_type_id, rt.reference, rt.posted_on
  • Index Cond: (rev.money_transfer_event_id = rt.auditable_event_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using monetary_endpoint_pkey on pegasus.monetary_endpoint dst (cost=0.43..0.59 rows=1 width=16) (never executed)

  • Output: dst.monetary_endpoint_id, dst.monetary_endpoint_type, dst.description, dst.account_id, dst.country_code, dst.card_detail_id, dst.card_program_id, dst.card_token, dst.generic_payee_id, dst.card_scheme_code, dst.currency, dst.execution_mode
  • Index Cond: (orig.destination_id = dst.monetary_endpoint_id)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using monetary_endpoint_pkey on pegasus.monetary_endpoint src (cost=0.43..0.59 rows=1 width=16) (never executed)

  • Output: src.monetary_endpoint_id, src.monetary_endpoint_type, src.description, src.account_id, src.country_code, src.card_detail_id, src.card_program_id, src.card_token, src.generic_payee_id, src.card_scheme_code, src.currency, src.execution_mode
  • Index Cond: (src.monetary_endpoint_id = orig.source_id)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using account_pkey on pegasus.account sa (cost=0.29..0.31 rows=1 width=32) (never executed)

  • Output: sa.account_id, sa.currency, sa.parent_summary_account_id, sa.account_description, sa.account_holder_id, sa.account_number, sa.balance_in_display, sa.balance_in_display_currency, sa.working_balance_in_display, sa.working_balance_in_display_currency, sa.disabled, sa.version, sa.account_type_id, sa.notification_threshold_amount, sa.created_on, sa.account_category_type_id
  • Index Cond: (sa.account_id = src.account_id)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using account_pkey on pegasus.account da (cost=0.29..0.31 rows=1 width=24) (never executed)

  • Output: da.account_id, da.currency, da.parent_summary_account_id, da.account_description, da.account_holder_id, da.account_number, da.balance_in_display, da.balance_in_display_currency, da.working_balance_in_display, da.working_balance_in_display_currency, da.disabled, da.version, da.account_type_id, da.notification_threshold_amount, da.created_on, da.account_category_type_id
  • Index Cond: (dst.account_id = da.account_id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_fee_auditable_event_money_transfer_event_id on pegasus.fee_auditable_event fe (cost=0.43..8.94 rows=6 width=18) (never executed)

  • Output: fe.fee_auditable_event_id, fe.money, fe.money_currency, fe.fee_type_id, fe.fee_fixed, fe.fee_fixed_currency, fe.fee_percentage, fe.fee_floor, fe.fee_floor_currency, fe.fee_ceiling, fe.fee_ceiling_currency, fe.fee_id, fe.auditable_parent_event_id, fe.money_transfer_event_id, fe.description, fe.fee_subtype_id
  • Index Cond: (rev.money_transfer_event_id = fe.money_transfer_event_id)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_account_entry_money_transfer_event_id on pegasus.account_entry saeo (cost=0.56..1.25 rows=11 width=24) (never executed)

  • Output: saeo.account_entry_id, saeo.account_id, saeo.account_transaction_id, saeo.money_in_global, saeo.money, saeo.money_in_global_currency, saeo.money_currency, saeo.description, saeo.date_posted, saeo.event_name, saeo.account_fee, saeo.running_balance, saeo.money_transfer_event_id, saeo.fee_event_id, saeo.date_inserted, saeo.money_converted, saeo.money_converted_currency
  • Index Cond: (orig.money_transfer_event_id = saeo.money_transfer_event_id)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using transaction_pkey on pegasus.account_transaction ot (cost=0.43..0.47 rows=1 width=24) (never executed)

  • Output: ot.account_transaction_id, ot.created_on, ot.auditable_event_id, ot.external_reference, ot.processor_type_id, ot.reference, ot.posted_on
  • Index Cond: (saeo.account_transaction_id = ot.account_transaction_id)
38. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.12..2.13 rows=1 width=28) (never executed)

  • Output: sc.company_name, sc.company_id
  • Sort Key: sc.company_id
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on pegasus.company sc (cost=0.00..2.11 rows=1 width=28) (never executed)

  • Output: sc.company_name, sc.company_id
  • Filter: ((sc.company_name)::text = 'Clearpay Limited'::text)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using event_type_pkey on pegasus.event_type et (cost=0.14..0.16 rows=1 width=23) (never executed)

  • Output: et.event_type_id, et.event_type_value, et.event_type_description, et.event_type_feeappliedonsource, et.event_type_alloweventretries, et.queueable, et.reversable, et.external, et.credit
  • Index Cond: (orig.event_type = et.event_type_id)
41. 0.055 512,761.662 ↑ 3.5 130 1

Subquery Scan on *SELECT* 2 (cost=1,840,137.20..1,840,184.45 rows=450 width=499) (actual time=512,761.405..512,761.662 rows=130 loops=1)

  • Output: "*SELECT* 2".original_transaction_date, "*SELECT* 2".reversal_date, "*SELECT* 2".original_reference, "*SELECT* 2".original_external_reference, "*SELECT* 2".reversal_amount, "*SELECT* 2".reversal_currency, "*SELECT* 2".reversal_fee, "*SELECT* 2".reversal_fee_currency, "*SELECT* 2".transaction_type, "*SELECT* 2".reversal_reference, "*SELECT* 2".source_account, "*SELECT* 2".dest_account, "*SELECT* 2".original_transaction_id, "*SELECT* 2".credit_currency, "*SELECT* 2".debit_currency, "*SELECT* 2".fee_curency, "*SELECT* 2".credit, "*SELECT* 2".debit, "*SELECT* 2".fee, "*SELECT* 2".reference, "*SELECT* 2".company_name, "*SELECT* 2".description, "*SELECT* 2".reversal_description, 'ROLLBACK'::text
42. 0.224 512,761.607 ↑ 3.5 130 1

Group (cost=1,840,137.20..1,840,176.57 rows=450 width=565) (actual time=512,761.398..512,761.607 rows=130 loops=1)

  • Output: orig_1.date_posted, rb.date_posted, ot_1.reference, mtae.trace_id, round(rb.money, 2), rb.money_currency, round(rb_fee.money, 2), rb_fee.money_currency, et_1.event_type_description, rt_1.reference, sa_1.account_number, da_1.account_number, ot_1.account_transaction_id, CASE WHEN ((orig_1.money > '0'::numeric) AND (NOT orig_1.account_fee)) THEN orig_1.money_currency ELSE NULL::bpchar END, CASE WHEN ((orig_1.money < '0'::numeric) AND (NOT orig_1.account_fee)) THEN orig_1.money_currency ELSE NULL::bpchar END, CASE WHEN ((orig_fee.money < '0'::numeric) AND orig_fee.account_fee) THEN orig_fee.money_currency ELSE NULL::bpchar END, round(CASE WHEN ((orig_1.money > '0'::numeric) AND (NOT orig_1.account_fee)) THEN orig_1.money ELSE NULL::numeric END, 2), round(CASE WHEN ((orig_1.money < '0'::numeric) AND (NOT orig_1.account_fee)) THEN orig_1.money ELSE NULL::numeric END, 2), round(CASE WHEN ((orig_fee.money < '0'::numeric) AND orig_fee.account_fee) THEN orig_fee.money ELSE NULL::numeric END, 2), ot_1.reference, c.company_name, mtae.description, rb.description, 'ROLLBACK'::text, orig_1.money, orig_1.money_currency, orig_fee.money, orig_fee.money_currency, rb.money, rb_fee.money, orig_1.description, orig_1.account_transaction_id, orig_1.account_fee, orig_fee.account_fee
  • Group Key: mtae.trace_id, orig_1.money, orig_1.money_currency, orig_fee.money, orig_fee.money_currency, rb.money, rb.money_currency, rb_fee.money, rb_fee.money_currency, rb.description, orig_1.description, orig_1.date_posted, rb.date_posted, orig_1.account_transaction_id, et_1.event_type_description, rt_1.reference, sa_1.account_number, da_1.account_number, ot_1.account_transaction_id, orig_1.account_fee, orig_fee.account_fee, c.company_name, mtae.description
43. 0.587 512,761.383 ↑ 3.5 130 1

Sort (cost=1,840,137.20..1,840,138.32 rows=450 width=261) (actual time=512,761.376..512,761.383 rows=130 loops=1)

  • Output: orig_1.date_posted, rb.date_posted, mtae.trace_id, rb.money_currency, rb_fee.money_currency, et_1.event_type_description, rt_1.reference, sa_1.account_number, da_1.account_number, ot_1.account_transaction_id, c.company_name, mtae.description, rb.description, orig_1.money, orig_1.money_currency, orig_fee.money, orig_fee.money_currency, rb.money, rb_fee.money, orig_1.description, orig_1.account_transaction_id, orig_1.account_fee, orig_fee.account_fee, ot_1.reference
  • Sort Key: mtae.trace_id, orig_1.money, orig_1.money_currency, orig_fee.money, orig_fee.money_currency, rb.money, rb.money_currency, rb_fee.money, rb_fee.money_currency, rb.description, orig_1.description, orig_1.date_posted, rb.date_posted, orig_1.account_transaction_id, et_1.event_type_description, rt_1.reference, sa_1.account_number, da_1.account_number, ot_1.account_transaction_id, orig_1.account_fee, orig_fee.account_fee, mtae.description
  • Sort Method: quicksort Memory: 59kB
44. 0.038 512,760.796 ↑ 3.5 130 1

Nested Loop Left Join (cost=1,803,011.04..1,840,117.37 rows=450 width=261) (actual time=511,730.183..512,760.796 rows=130 loops=1)

  • Output: orig_1.date_posted, rb.date_posted, mtae.trace_id, rb.money_currency, rb_fee.money_currency, et_1.event_type_description, rt_1.reference, sa_1.account_number, da_1.account_number, ot_1.account_transaction_id, c.company_name, mtae.description, rb.description, orig_1.money, orig_1.money_currency, orig_fee.money, orig_fee.money_currency, rb.money, rb_fee.money, orig_1.description, orig_1.account_transaction_id, orig_1.account_fee, orig_fee.account_fee, ot_1.reference
45. 0.055 512,758.288 ↓ 2.5 130 1

Nested Loop Left Join (cost=1,803,010.48..1,839,255.71 rows=53 width=250) (actual time=511,730.151..512,758.288 rows=130 loops=1)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, c.company_name, rb.date_posted, rb.money, rb.money_currency, rb.description, rt_1.reference, rb_fee.money, rb_fee.money_currency
  • Inner Unique: true
46. 291.975 512,757.063 ↓ 2.5 130 1

Merge Join (cost=1,803,010.05..1,838,812.11 rows=53 width=242) (actual time=511,730.071..512,757.063 rows=130 loops=1)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, c.company_name, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id, rb_fee.money, rb_fee.money_currency
  • Inner Unique: true
  • Merge Cond: (ah.company_id = c.company_id)
47. 16,022.176 512,465.047 ↓ 501.2 2,381,413 1

Nested Loop (cost=1,803,007.93..2,984,214.53 rows=4,751 width=230) (actual time=15,196.630..512,465.047 rows=2,381,413 loops=1)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, ah.company_id, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id, rb_fee.money, rb_fee.money_currency
  • Join Filter: (a.account_holder_id = ah.account_holder_id)
  • Rows Removed by Join Filter: 108977045
48. 0.530 0.530 ↑ 62.6 47 1

Index Scan using fki_client_id_fk on pegasus.account_holder ah (cost=0.28..3,014.72 rows=2,942 width=16) (actual time=0.025..0.530 rows=47 loops=1)

  • Output: ah.account_holder_id, ah.parent_id, ah.created_on, ah.last_updated, ah.company_id, ah.fees_currency, ah.email_address, ah.account_holder_type_id, ah.owner_user_id, ah.account_holder_number, ah.kyc_level_id, ah.default_account_id, ah.default_card_payee_id, ah.default_bank_payee_id, ah.max_number_accounts
49. 18,672.127 496,442.341 ↓ 498.7 2,369,329 47

Materialize (cost=1,803,007.65..2,771,550.06 rows=4,751 width=230) (actual time=323.337..10,562.603 rows=2,369,329 loops=47)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, a.account_holder_id, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id, rb_fee.money, rb_fee.money_currency
50. 709.326 477,770.214 ↓ 501.3 2,381,840 1

Gather (cost=1,803,007.65..2,771,526.30 rows=4,751 width=230) (actual time=15,196.590..477,770.214 rows=2,381,840 loops=1)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, a.account_holder_id, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id, rb_fee.money, rb_fee.money_currency
  • Workers Planned: 4
  • Workers Launched: 4
51. 452,949.962 477,060.888 ↓ 401.0 476,368 5

Nested Loop Left Join (cost=1,802,007.65..2,770,051.20 rows=1,188 width=230) (actual time=15,359.927..477,060.888 rows=476,368 loops=5)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, a.account_holder_id, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id, rb_fee.money, rb_fee.money_currency
  • Worker 0: actual time=15179.096..478062.229 rows=472154 loops=1
  • Worker 1: actual time=15180.186..477369.634 rows=485177 loops=1
  • Worker 2: actual time=15179.806..478700.810 rows=477557 loops=1
  • Worker 3: actual time=16067.749..476952.964 rows=472737 loops=1
52. 1,877.546 24,109.976 ↓ 401.0 476,368 5

Nested Loop (cost=1,802,007.08..2,750,820.31 rows=1,188 width=220) (actual time=15,356.262..24,109.976 rows=476,368 loops=5)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, ot_1.reference, ot_1.account_transaction_id, a.account_holder_id, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id
  • Inner Unique: true
  • Worker 0: actual time=15175.108..25216.787 rows=472154 loops=1
  • Worker 1: actual time=15176.745..24699.431 rows=485177 loops=1
  • Worker 2: actual time=15175.814..22693.025 rows=477557 loops=1
  • Worker 3: actual time=16064.316..24653.653 rows=472737 loops=1
53. 736.266 22,232.427 ↓ 401.0 476,368 5

Parallel Hash Join (cost=1,802,006.79..2,747,972.26 rows=1,188 width=220) (actual time=15,356.224..22,232.427 rows=476,368 loops=5)

  • Output: mtae.trace_id, mtae.description, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, orig_1.account_id, ot_1.reference, ot_1.account_transaction_id, rb.date_posted, rb.money, rb.money_currency, rb.description, rb.account_transaction_id
  • Hash Cond: (mtae.money_transfer_event_id = rb.money_transfer_event_id)
  • Worker 0: actual time=15175.066..23375.204 rows=472154 loops=1
  • Worker 1: actual time=15176.712..22868.637 rows=485177 loops=1
  • Worker 2: actual time=15175.771..20801.602 rows=477557 loops=1
  • Worker 3: actual time=16064.273..22767.409 rows=472737 loops=1
54. 1,541.223 18,781.354 ↑ 2.9 2,793,288 5

Parallel Hash Left Join (cost=924,947.27..1,791,245.67 rows=7,966,576 width=186) (actual time=12,453.915..18,781.354 rows=2,793,288 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.money_transfer_event_id, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, orig_1.money_transfer_event_id, orig_1.account_id, ot_1.reference, ot_1.account_transaction_id
  • Inner Unique: true
  • Hash Cond: (orig_1.account_transaction_id = ot_1.account_transaction_id)
  • Worker 0: actual time=12453.941..19847.892 rows=3326331 loops=1
  • Worker 1: actual time=12453.905..19373.068 rows=3037454 loops=1
  • Worker 2: actual time=12453.936..17394.434 rows=2153835 loops=1
  • Worker 3: actual time=12453.877..19267.600 rows=3170263 loops=1
55. 2,521.829 16,026.502 ↑ 2.9 2,793,288 5

Parallel Hash Join (cost=795,061.70..1,640,447.83 rows=7,966,576 width=162) (actual time=11,222.899..16,026.502 rows=2,793,288 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.money_transfer_event_id, sa_1.account_number, da_1.account_number, et_1.event_type_description, orig_1.date_posted, orig_1.money, orig_1.account_fee, orig_1.money_currency, orig_1.description, orig_1.account_transaction_id, orig_1.money_transfer_event_id, orig_1.account_id
  • Hash Cond: (orig_1.money_transfer_event_id = mtae.money_transfer_event_id)
  • Worker 0: actual time=11222.910..16870.917 rows=3326331 loops=1
  • Worker 1: actual time=11222.866..16457.063 rows=3037454 loops=1
  • Worker 2: actual time=11222.933..14952.867 rows=2153835 loops=1
  • Worker 3: actual time=11222.894..16406.255 rows=3170263 loops=1
56. 2,295.350 2,295.350 ↑ 1.3 6,372,827 5

Parallel Seq Scan on pegasus.account_entry orig_1 (cost=0.00..796,866.36 rows=7,966,576 width=67) (actual time=0.022..2,295.350 rows=6,372,827 loops=5)

  • Output: orig_1.account_entry_id, orig_1.account_id, orig_1.account_transaction_id, orig_1.money_in_global, orig_1.money, orig_1.money_in_global_currency, orig_1.money_currency, orig_1.description, orig_1.date_posted, orig_1.event_name, orig_1.account_fee, orig_1.running_balance, orig_1.money_transfer_event_id, orig_1.fee_event_id, orig_1.date_inserted, orig_1.money_converted, orig_1.money_converted_currency
  • Filter: (orig_1.event_name !~~ '%rollback%'::text)
  • Rows Removed by Filter: 40597
  • Worker 0: actual time=0.023..2711.885 rows=7420713 loops=1
  • Worker 1: actual time=0.023..2458.810 rows=6678251 loops=1
  • Worker 2: actual time=0.022..1752.374 rows=4957388 loops=1
  • Worker 3: actual time=0.023..2492.775 rows=7191866 loops=1
57. 1,050.923 11,209.323 ↑ 1.2 1,405,949 5

Parallel Hash (cost=773,096.31..773,096.31 rows=1,757,231 width=95) (actual time=11,209.323..11,209.323 rows=1,405,949 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.money_transfer_event_id, sa_1.account_number, da_1.account_number, et_1.event_type_description
  • Buckets: 8388608 Batches: 1 Memory Usage: 958688kB
  • Worker 0: actual time=11222.778..11222.778 rows=1424171 loops=1
  • Worker 1: actual time=11222.734..11222.734 rows=1441584 loops=1
  • Worker 2: actual time=11155.503..11155.503 rows=1346133 loops=1
  • Worker 3: actual time=11222.812..11222.812 rows=1385654 loops=1
58. 355.327 10,158.400 ↑ 1.2 1,405,949 5

Hash Left Join (cost=586,095.37..773,096.31 rows=1,757,231 width=95) (actual time=5,872.971..10,158.400 rows=1,405,949 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.money_transfer_event_id, sa_1.account_number, da_1.account_number, et_1.event_type_description
  • Inner Unique: true
  • Hash Cond: (mtae.event_type = et_1.event_type_id)
  • Worker 0: actual time=5872.944..10154.528 rows=1424171 loops=1
  • Worker 1: actual time=5872.902..10155.046 rows=1441584 loops=1
  • Worker 2: actual time=5873.151..10174.994 rows=1346133 loops=1
  • Worker 3: actual time=5872.949..10155.496 rows=1385654 loops=1
59. 454.291 9,803.027 ↑ 1.2 1,405,949 5

Parallel Hash Left Join (cost=586,093.65..767,870.96 rows=1,757,231 width=80) (actual time=5,872.826..9,803.027 rows=1,405,949 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.event_type, mtae.money_transfer_event_id, sa_1.account_number, da_1.account_number
  • Inner Unique: true
  • Hash Cond: (dst_1.account_id = da_1.account_id)
  • Worker 0: actual time=5872.795..9794.301 rows=1424171 loops=1
  • Worker 1: actual time=5872.748..9808.128 rows=1441584 loops=1
  • Worker 2: actual time=5872.966..9811.442 rows=1346133 loops=1
  • Worker 3: actual time=5872.815..9797.729 rows=1385654 loops=1
60. 478.677 9,317.035 ↑ 1.2 1,405,949 5

Parallel Hash Left Join (cost=578,213.58..755,377.62 rows=1,757,231 width=72) (actual time=5,840.923..9,317.035 rows=1,405,949 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.event_type, mtae.money_transfer_event_id, dst_1.account_id, sa_1.account_number
  • Inner Unique: true
  • Hash Cond: (src_1.account_id = sa_1.account_id)
  • Worker 0: actual time=5840.906..9302.923 rows=1424171 loops=1
  • Worker 1: actual time=5840.924..9330.976 rows=1441584 loops=1
  • Worker 2: actual time=5840.956..9323.888 rows=1346133 loops=1
  • Worker 3: actual time=5840.908..9312.266 rows=1385654 loops=1
61. 1,216.616 8,828.550 ↑ 1.2 1,405,949 5

Parallel Hash Left Join (cost=570,333.51..742,884.28 rows=1,757,231 width=64) (actual time=5,830.954..8,828.550 rows=1,405,949 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.event_type, mtae.money_transfer_event_id, dst_1.account_id, src_1.account_id
  • Inner Unique: true
  • Hash Cond: (mtae.source_id = src_1.monetary_endpoint_id)
  • Worker 0: actual time=5830.918..8805.809 rows=1424171 loops=1
  • Worker 1: actual time=5830.964..8852.698 rows=1441584 loops=1
  • Worker 2: actual time=5830.974..8825.284 rows=1346133 loops=1
  • Worker 3: actual time=5830.957..8823.918 rows=1385654 loops=1
62. 1,434.890 4,248.296 ↑ 1.2 1,405,949 5

Parallel Hash Left Join (cost=285,166.76..453,104.80 rows=1,757,231 width=64) (actual time=2,448.980..4,248.296 rows=1,405,949 loops=5)

  • Output: mtae.trace_id, mtae.description, mtae.source_id, mtae.event_type, mtae.money_transfer_event_id, dst_1.account_id
  • Inner Unique: true
  • Hash Cond: (mtae.destination_id = dst_1.monetary_endpoint_id)
  • Worker 0: actual time=2448.965..4229.008 rows=1424171 loops=1
  • Worker 1: actual time=2448.995..4254.505 rows=1441584 loops=1
  • Worker 2: actual time=2449.017..4246.683 rows=1346133 loops=1
  • Worker 3: actual time=2448.974..4248.170 rows=1385654 loops=1
63. 396.980 396.980 ↑ 1.2 1,405,949 5

Parallel Seq Scan on pegasus.money_transfer_auditable_event mtae (cost=0.00..163,325.31 rows=1,757,231 width=64) (actual time=0.014..396.980 rows=1,405,949 loops=5)

  • Output: mtae.money_transfer_event_id, mtae.money, mtae.money_currency, mtae.description, mtae.source_id, mtae.destination_id, mtae.when_occurred, mtae.when_noticed, mtae.auditable_event_state_type_id, mtae.trace_id, mtae.event_type, mtae.initiator_id, mtae.initiator_user_id, mtae.auto_release_on, mtae.original_trace_id, mtae.payment_category_type_id, mtae.kyc_id
  • Worker 0: actual time=0.012..407.638 rows=1424171 loops=1
  • Worker 1: actual time=0.015..389.784 rows=1441584 loops=1
  • Worker 2: actual time=0.018..401.360 rows=1346133 loops=1
  • Worker 3: actual time=0.013..395.221 rows=1385654 loops=1
64. 1,551.183 2,416.426 ↑ 1.3 2,812,365 5

Parallel Hash (cost=241,193.55..241,193.55 rows=3,517,856 width=16) (actual time=2,416.426..2,416.426 rows=2,812,365 loops=5)

  • Output: dst_1.monetary_endpoint_id, dst_1.account_id
  • Buckets: 16777216 Batches: 1 Memory Usage: 790688kB
  • Worker 0: actual time=2286.649..2286.649 rows=3672605 loops=1
  • Worker 1: actual time=2448.883..2448.883 rows=2340312 loops=1
  • Worker 2: actual time=2448.871..2448.871 rows=2290701 loops=1
  • Worker 3: actual time=2448.862..2448.862 rows=2691934 loops=1
65. 865.243 865.243 ↑ 1.3 2,812,365 5

Parallel Seq Scan on pegasus.monetary_endpoint dst_1 (cost=0.00..241,193.55 rows=3,517,856 width=16) (actual time=0.026..865.243 rows=2,812,365 loops=5)

  • Output: dst_1.monetary_endpoint_id, dst_1.account_id
  • Worker 0: actual time=0.030..859.422 rows=3672605 loops=1
  • Worker 1: actual time=0.027..871.590 rows=2340312 loops=1
  • Worker 2: actual time=0.031..875.223 rows=2290701 loops=1
  • Worker 3: actual time=0.025..869.990 rows=2691934 loops=1
66. 1,732.697 3,363.638 ↑ 1.3 2,812,365 5

Parallel Hash (cost=241,193.55..241,193.55 rows=3,517,856 width=16) (actual time=3,363.638..3,363.638 rows=2,812,365 loops=5)

  • Output: src_1.monetary_endpoint_id, src_1.account_id
  • Buckets: 16777216 Batches: 1 Memory Usage: 790688kB
  • Worker 0: actual time=3381.795..3381.795 rows=2733007 loops=1
  • Worker 1: actual time=3381.804..3381.805 rows=2849700 loops=1
  • Worker 2: actual time=3381.788..3381.788 rows=2707701 loops=1
  • Worker 3: actual time=3290.990..3290.991 rows=3072988 loops=1
67. 1,630.941 1,630.941 ↑ 1.3 2,812,365 5

Parallel Seq Scan on pegasus.monetary_endpoint src_1 (cost=0.00..241,193.55 rows=3,517,856 width=16) (actual time=0.382..1,630.941 rows=2,812,365 loops=5)

  • Output: src_1.monetary_endpoint_id, src_1.account_id
  • Worker 0: actual time=0.277..1631.179 rows=2733007 loops=1
  • Worker 1: actual time=0.295..1617.288 rows=2849700 loops=1
  • Worker 2: actual time=0.667..1618.877 rows=2707701 loops=1
  • Worker 3: actual time=0.023..1645.395 rows=3072988 loops=1
68. 3.277 9.808 ↑ 2.1 7,298 5

Parallel Hash (cost=7,690.03..7,690.03 rows=15,203 width=24) (actual time=9.808..9.808 rows=7,298 loops=5)

  • Output: sa_1.account_number, sa_1.account_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2880kB
  • Worker 0: actual time=9.903..9.903 rows=5806 loops=1
  • Worker 1: actual time=9.873..9.874 rows=5856 loops=1
  • Worker 2: actual time=9.886..9.886 rows=5892 loops=1
  • Worker 3: actual time=9.904..9.905 rows=11566 loops=1
69. 6.531 6.531 ↑ 2.1 7,298 5

Parallel Seq Scan on pegasus.account sa_1 (cost=0.00..7,690.03 rows=15,203 width=24) (actual time=0.017..6.531 rows=7,298 loops=5)

  • Output: sa_1.account_number, sa_1.account_id
  • Worker 0: actual time=0.019..6.448 rows=5806 loops=1
  • Worker 1: actual time=0.031..6.505 rows=5856 loops=1
  • Worker 2: actual time=0.015..6.423 rows=5892 loops=1
  • Worker 3: actual time=0.011..6.593 rows=11566 loops=1
70. 21.878 31.701 ↑ 2.1 7,298 5

Parallel Hash (cost=7,690.03..7,690.03 rows=15,203 width=24) (actual time=31.701..31.701 rows=7,298 loops=5)

  • Output: da_1.account_number, da_1.account_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2880kB
  • Worker 0: actual time=31.780..31.780 rows=6232 loops=1
  • Worker 1: actual time=31.706..31.706 rows=6101 loops=1
  • Worker 2: actual time=31.385..31.385 rows=6346 loops=1
  • Worker 3: actual time=31.810..31.811 rows=10810 loops=1
71. 9.823 9.823 ↑ 2.1 7,298 5

Parallel Seq Scan on pegasus.account da_1 (cost=0.00..7,690.03 rows=15,203 width=24) (actual time=0.030..9.823 rows=7,298 loops=5)

  • Output: da_1.account_number, da_1.account_id
  • Worker 0: actual time=0.031..9.739 rows=6232 loops=1
  • Worker 1: actual time=0.033..9.586 rows=6101 loops=1
  • Worker 2: actual time=0.037..10.029 rows=6346 loops=1
  • Worker 3: actual time=0.021..9.943 rows=10810 loops=1
72. 0.014 0.046 ↑ 1.0 32 5

Hash (cost=1.32..1.32 rows=32 width=23) (actual time=0.046..0.046 rows=32 loops=5)

  • Output: et_1.event_type_description, et_1.event_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Worker 0: actual time=0.046..0.046 rows=32 loops=1
  • Worker 1: actual time=0.050..0.050 rows=32 loops=1
  • Worker 2: actual time=0.061..0.061 rows=32 loops=1
  • Worker 3: actual time=0.041..0.042 rows=32 loops=1
73. 0.032 0.032 ↑ 1.0 32 5

Seq Scan on pegasus.event_type et_1 (cost=0.00..1.32 rows=32 width=23) (actual time=0.026..0.032 rows=32 loops=5)

  • Output: et_1.event_type_description, et_1.event_type_id
  • Worker 0: actual time=0.032..0.035 rows=32 loops=1
  • Worker 1: actual time=0.030..0.035 rows=32 loops=1
  • Worker 2: actual time=0.035..0.043 rows=32 loops=1
  • Worker 3: actual time=0.025..0.030 rows=32 loops=1
74. 755.804 1,213.629 ↑ 1.3 1,396,388 5

Parallel Hash (cost=108,063.03..108,063.03 rows=1,745,803 width=24) (actual time=1,213.629..1,213.629 rows=1,396,388 loops=5)

  • Output: ot_1.reference, ot_1.account_transaction_id
  • Buckets: 8388608 Batches: 1 Memory Usage: 448224kB
  • Worker 0: actual time=1144.439..1144.439 rows=1777178 loops=1
  • Worker 1: actual time=1230.936..1230.936 rows=1559920 loops=1
  • Worker 2: actual time=1230.923..1230.923 rows=1232287 loops=1
  • Worker 3: actual time=1230.886..1230.886 rows=1224567 loops=1
75. 457.825 457.825 ↑ 1.3 1,396,388 5

Parallel Seq Scan on pegasus.account_transaction ot_1 (cost=0.00..108,063.03 rows=1,745,803 width=24) (actual time=0.032..457.825 rows=1,396,388 loops=5)

  • Output: ot_1.reference, ot_1.account_transaction_id
  • Worker 0: actual time=0.047..454.632 rows=1777178 loops=1
  • Worker 1: actual time=0.025..445.820 rows=1559920 loops=1
  • Worker 2: actual time=0.025..464.870 rows=1232287 loops=1
  • Worker 3: actual time=0.039..457.238 rows=1224567 loops=1
76. 270.803 2,714.807 ↓ 6.1 1,600 5

Parallel Hash (cost=877,056.25..877,056.25 rows=262 width=58) (actual time=2,714.807..2,714.807 rows=1,600 loops=5)

  • Output: rb.date_posted, rb.money, rb.money_currency, rb.description, rb.money_transfer_event_id, rb.account_transaction_id
  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 688kB
  • Worker 0: actual time=2711.266..2711.266 rows=1728 loops=1
  • Worker 1: actual time=2712.507..2712.507 rows=1857 loops=1
  • Worker 2: actual time=2712.721..2712.721 rows=1700 loops=1
  • Worker 3: actual time=2712.583..2712.583 rows=1414 loops=1
77. 2,444.004 2,444.004 ↓ 6.1 1,600 5

Parallel Seq Scan on pegasus.account_entry rb (cost=0.00..877,056.25 rows=262 width=58) (actual time=777.811..2,444.004 rows=1,600 loops=5)

  • Output: rb.date_posted, rb.money, rb.money_currency, rb.description, rb.money_transfer_event_id, rb.account_transaction_id
  • Filter: ((rb.event_name ~~ '%rollback%'::text) AND (date(rb.date_posted) >= '2019-01-01'::date) AND (date(rb.date_posted) <= '2019-01-31'::date))
  • Rows Removed by Filter: 6411824
  • Worker 0: actual time=869.060..2069.947 rows=1728 loops=1
  • Worker 1: actual time=755.125..2707.887 rows=1857 loops=1
  • Worker 2: actual time=755.293..2075.718 rows=1700 loops=1
  • Worker 3: actual time=743.440..2679.882 rows=1414 loops=1
78. 0.003 0.003 ↑ 1.0 1 2,381,840

Index Scan using account_pkey on pegasus.account a (cost=0.29..2.40 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=2,381,840)

  • Output: a.account_id, a.currency, a.parent_summary_account_id, a.account_description, a.account_holder_id, a.account_number, a.balance_in_display, a.balance_in_display_currency, a.working_balance_in_display, a.working_balance_in_display_currency, a.disabled, a.version, a.account_type_id, a.notification_threshold_amount, a.created_on, a.account_category_type_id
  • Index Cond: (a.account_id = orig_1.account_id)
  • Worker 0: actual time=0.003..0.003 rows=1 loops=472154
  • Worker 1: actual time=0.003..0.003 rows=1 loops=485177
  • Worker 2: actual time=0.003..0.003 rows=1 loops=477557
  • Worker 3: actual time=0.003..0.003 rows=1 loops=472737
79. 0.950 0.950 ↓ 0.0 0 2,381,840

Index Scan using idx_account_entry_account_transaction_id on pegasus.account_entry rb_fee (cost=0.56..16.18 rows=1 width=18) (actual time=0.950..0.950 rows=0 loops=2,381,840)

  • Output: rb_fee.account_entry_id, rb_fee.account_id, rb_fee.account_transaction_id, rb_fee.money_in_global, rb_fee.money, rb_fee.money_in_global_currency, rb_fee.money_currency, rb_fee.description, rb_fee.date_posted, rb_fee.event_name, rb_fee.account_fee, rb_fee.running_balance, rb_fee.money_transfer_event_id, rb_fee.fee_event_id, rb_fee.date_inserted, rb_fee.money_converted, rb_fee.money_converted_currency
  • Index Cond: (orig_1.account_transaction_id = rb_fee.account_transaction_id)
  • Filter: (rb_fee.account_fee AND (rb_fee.event_name ~~ '%rollback%'::text) AND (rb_fee.money > '0'::numeric))
  • Rows Removed by Filter: 1539
  • Worker 0: actual time=0.958..0.958 rows=0 loops=472154
  • Worker 1: actual time=0.932..0.932 rows=0 loops=485177
  • Worker 2: actual time=0.954..0.954 rows=0 loops=477557
  • Worker 3: actual time=0.956..0.956 rows=0 loops=472737
80. 0.023 0.041 ↑ 1.0 1 1

Sort (cost=2.12..2.13 rows=1 width=28) (actual time=0.040..0.041 rows=1 loops=1)

  • Output: c.company_name, c.company_id
  • Sort Key: c.company_id
  • Sort Method: quicksort Memory: 25kB
81. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on pegasus.company c (cost=0.00..2.11 rows=1 width=28) (actual time=0.016..0.018 rows=1 loops=1)

  • Output: c.company_name, c.company_id
  • Filter: ((c.company_name)::text = 'Clearpay Limited'::text)
  • Rows Removed by Filter: 88
82. 1.170 1.170 ↑ 1.0 1 130

Index Scan using transaction_pkey on pegasus.account_transaction rt_1 (cost=0.43..8.37 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=130)

  • Output: rt_1.account_transaction_id, rt_1.created_on, rt_1.auditable_event_id, rt_1.external_reference, rt_1.processor_type_id, rt_1.reference, rt_1.posted_on
  • Index Cond: (rb.account_transaction_id = rt_1.account_transaction_id)
83. 2.470 2.470 ↑ 8.0 1 130

Index Scan using idx_account_entry_account_transaction_id on pegasus.account_entry orig_fee (cost=0.56..16.18 rows=8 width=19) (actual time=0.012..0.019 rows=1 loops=130)

  • Output: orig_fee.account_entry_id, orig_fee.account_id, orig_fee.account_transaction_id, orig_fee.money_in_global, orig_fee.money, orig_fee.money_in_global_currency, orig_fee.money_currency, orig_fee.description, orig_fee.date_posted, orig_fee.event_name, orig_fee.account_fee, orig_fee.running_balance, orig_fee.money_transfer_event_id, orig_fee.fee_event_id, orig_fee.date_inserted, orig_fee.money_converted, orig_fee.money_converted_currency
  • Index Cond: (orig_1.account_transaction_id = orig_fee.account_transaction_id)
  • Filter: (orig_fee.account_fee AND (orig_fee.event_name !~~ '%rollback%'::text) AND (orig_fee.money < '0'::numeric))
  • Rows Removed by Filter: 9