explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RtxA

Settings
# exclusive inclusive rows x rows loops node
1. 11.354 788.812 ↑ 3.9 177 1

GroupAggregate (cost=14,138.98..14,319.44 rows=690 width=40) (actual time=753.372..788.812 rows=177 loops=1)

  • Output: customer2_.id, sum(drivertran0_.for_monthly_reimbursement)
  • Group Key: customer2_.id
  • Buffers: shared hit=137,929 read=36,637
2. 15.114 777.458 ↓ 13.0 28,724 1

Merge Join (cost=14,138.98..14,285.99 rows=2,204 width=14) (actual time=753.291..777.458 rows=28,724 loops=1)

  • Output: customer2_.id, drivertran0_.for_monthly_reimbursement
  • Merge Cond: (customer2_.id = driver1_.customer)
  • Buffers: shared hit=137,929 read=36,637
3. 0.609 0.609 ↑ 1.0 661 1

Index Only Scan using mbs_customer_pkey on public.mbs_customer customer2_ (cost=0.28..68.42 rows=690 width=8) (actual time=0.024..0.609 rows=661 loops=1)

  • Output: customer2_.id
  • Heap Fetches: 259
  • Buffers: shared hit=208 read=18
4. 26.387 761.735 ↓ 13.0 28,724 1

Sort (cost=14,138.71..14,144.22 rows=2,204 width=14) (actual time=753.260..761.735 rows=28,724 loops=1)

  • Output: drivertran0_.for_monthly_reimbursement, driver1_.customer
  • Sort Key: driver1_.customer
  • Sort Method: quicksort Memory: 2,115kB
  • Buffers: shared hit=137,721 read=36,619
5. 29.683 735.348 ↓ 13.0 28,724 1

Nested Loop (cost=42.48..14,016.32 rows=2,204 width=14) (actual time=452.979..735.348 rows=28,724 loops=1)

  • Output: drivertran0_.for_monthly_reimbursement, driver1_.customer
  • Buffers: shared hit=137,721 read=36,619
6. 15.725 562.045 ↓ 13.0 28,724 1

Nested Loop (cost=42.06..7,763.55 rows=2,204 width=14) (actual time=452.952..562.045 rows=28,724 loops=1)

  • Output: drivertran0_.for_monthly_reimbursement, drivertran0_.driver
  • Buffers: shared hit=26,155 read=28,740
7. 4.110 18.898 ↓ 12.7 2,611 1

Nested Loop (cost=41.64..233.16 rows=205 width=8) (actual time=0.512..18.898 rows=2,611 loops=1)

  • Output: approvedpc3_.id
  • Buffers: shared hit=7,982 read=186
8. 3.199 7.021 ↓ 13.8 2,589 1

Merge Join (cost=41.36..154.28 rows=188 width=8) (actual time=0.506..7.021 rows=2,589 loops=1)

  • Output: paymenttra4_.id
  • Merge Cond: (paymenttra4_.nacha_response = nacharespo5_.id)
  • Buffers: shared hit=284 read=98
9. 2.086 2.086 ↑ 1.0 2,590 1

Index Scan using mbs_payment_transaction_nacha_response_index on public.mbs_payment_transaction paymenttra4_ (cost=0.28..240.21 rows=2,609 width=16) (actual time=0.013..2.086 rows=2,590 loops=1)

  • Output: paymenttra4_.id, paymenttra4_.created_date, paymenttra4_.deleted, paymenttra4_.last_modified_date, paymenttra4_.motus_id, paymenttra4_.version, paymenttra4_.payment_txn_id, paymenttra4_.payout_date, paymenttra4_.created_by, paymenttra4_.last_modified_by, paymenttra4_.customer, paymenttra4_.nacha_response, paymenttra4_.system_origin
  • Buffers: shared hit=283 read=77
10. 1.412 1.736 ↓ 11.9 2,627 1

Sort (cost=41.06..41.61 rows=220 width=8) (actual time=0.463..1.736 rows=2,627 loops=1)

  • Output: nacharespo5_.id
  • Sort Key: nacharespo5_.id
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=1 read=21
11. 0.324 0.324 ↓ 1.2 271 1

Index Scan using mbs_nacha_response_status_code_index on public.mbs_nacha_response nacharespo5_ (cost=0.28..32.50 rows=220 width=8) (actual time=0.041..0.324 rows=271 loops=1)

  • Output: nacharespo5_.id
  • Index Cond: (nacharespo5_.status_code = '200'::bigint)
  • Buffers: shared hit=1 read=21
12. 7.767 7.767 ↑ 1.0 1 2,589

Index Scan using mbs_approved_pcr_report_payment_transaction_index on public.mbs_approved_pcr_report approvedpc3_ (cost=0.28..0.39 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=2,589)

  • Output: approvedpc3_.id, approvedpc3_.created_date, approvedpc3_.deleted, approvedpc3_.last_modified_date, approvedpc3_.motus_id, approvedpc3_.version, approvedpc3_.adjustment_amount, approvedpc3_.approval_date, approvedpc3_.mrr_amount, approvedpc3_.total_pcr_amount, approvedpc3_.transaction_status, approvedpc3_.created_by, approvedpc3_.last_modified_by, approvedpc3_.customer, approvedpc3_.previously_denied_mrr_amount, approvedpc3_.payment_transaction, approvedpc3_.mrr_fund_received_amount, approvedpc3_.fuel_adjustment_amount, approvedpc3_.manual_adjustment_amount, approvedpc3_.automatic_adjustment_amount, approvedpc3_.fuel_tranche_amount, approvedpc3_.fuel_unspent_amount, approvedpc3_.job_uuid, approvedpc3_.system_origin
  • Index Cond: (approvedpc3_.payment_transaction = paymenttra4_.id)
  • Buffers: shared hit=7,698 read=88
13. 527.422 527.422 ↓ 1.4 11 2,611

Index Scan using mbs_driver_transaction_approved_pcr_report_index on public.mbs_driver_transaction drivertran0_ (cost=0.42..36.49 rows=8 width=22) (actual time=0.167..0.202 rows=11 loops=2,611)

  • Output: drivertran0_.for_monthly_reimbursement, drivertran0_.driver, drivertran0_.approved_pcr_report
  • Index Cond: (drivertran0_.approved_pcr_report = approvedpc3_.id)
  • Filter: ((NOT drivertran0_.unconsumed) AND (drivertran0_.transaction_date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (drivertran0_.transaction_date <= '2020-01-31 23:59:59.999'::timestamp without time zone) AND ((drivertran0_.status)::text = 'APPROVED'::text) AND ((drivertran0_.transaction_type)::text = ANY ('{RECEIVED,PAID_AGAINST_MRR,BORROWED,PAID_TO_MOTUS,BACKPAY,WITH_ZERO_BACKPAY,REVERSAL,MISCELLANEOUS_PAID,CLIENT_CREDIT,MISCELLANEOUS,PCR_ADJUSTMENT,PAYMENT_RETURN,UNCHECK_FROM_PCR,RE_CHECK_IN_PCR,LAST_APPROVED_PCR,FUEL,FUEL_FUNDS_TRANSFER,MRR_LATE_UPDATE,MRR_DENIED}'::text[])))
  • Rows Removed by Filter: 147
  • Buffers: shared hit=18,173 read=28,554
14. 143.620 143.620 ↑ 1.0 1 28,724

Index Scan using mbs_driver_pkey on public.mbs_driver driver1_ (cost=0.42..2.81 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=28,724)

  • Output: driver1_.id, driver1_.customer
  • Index Cond: (driver1_.id = drivertran0_.driver)
  • Buffers: shared hit=111,566 read=7,879
Planning time : 2.014 ms
Execution time : 789.000 ms