explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R8H5

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=155,849.70..155,849.72 rows=1 width=13) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=155,849.70..155,849.71 rows=1 width=13) (actual rows= loops=)

  • Sort Key: ap.payment_number DESC, ap.id, ah1.id
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=85,194.45..155,849.69 rows=1 width=13) (actual rows= loops=)

  • Join Filter: (ad.id = aa.credit_ap_detail_id)
4. 0.000 0.000 ↓ 0.0

Merge Join (cost=85,194.02..155,848.34 rows=1 width=29) (actual rows= loops=)

  • Merge Cond: (ah1.ap_payment_id = ap.id)
  • Join Filter: (jp.id = ad1.job_phase_id)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=85,193.32..262,204.86 rows=151,232 width=32) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=82,324.84..159,485.77 rows=1 width=20) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Subquery Scan on ah1 (cost=82,324.41..159,477.30 rows=1 width=20) (actual rows= loops=)

  • Filter: ((ah1.row_number = 1) AND (ah1.ap_payee_id = 248497) AND (NOT (SubPlan 1)))
8. 0.000 0.000 ↓ 0.0

WindowAgg (cost=82,324.41..82,501.94 rows=7,890 width=1,540) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=82,324.41..82,344.14 rows=7,890 width=24) (actual rows= loops=)

  • Sort Key: ah.ap_payment_id, ah.id DESC
10. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ap_headers ah (cost=50,206.84..81,813.70 rows=7,890 width=24) (actual rows= loops=)

  • Recheck Cond: ((cid = 9848) AND (ap_payment_id IS NOT NULL))
  • Filter: CASE WHEN (reversal_ap_header_id IS NOT NULL) THEN (NOT (hashed SubPlan 2)) ELSE true END
11. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=9,161.00..9,161.00 rows=15,780 width=0) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on pk_ap_headers (cost=0.00..799.81 rows=43,118 width=0) (actual rows= loops=)

  • Index Cond: (cid = 9848)
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_ap_headers_ap_payment_id_partial (cost=0.00..8,356.99 rows=522,513 width=0) (actual rows= loops=)

14.          

SubPlan (for Bitmap Heap Scan)

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=40,850.70..41,006.81 rows=15,611 width=12) (actual rows= loops=)

  • Group Key: ap_headers.cid, ap_headers.ap_payment_id
16. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ap_headers (cost=9,164.94..40,732.35 rows=15,780 width=12) (actual rows= loops=)

  • Recheck Cond: ((cid = 9848) AND (ap_payment_id IS NOT NULL))
17. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=9,164.94..9,164.94 rows=15,780 width=0) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on pk_ap_headers (cost=0.00..799.81 rows=43,118 width=0) (actual rows= loops=)

  • Index Cond: (cid = 9848)
19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_ap_headers_ap_payment_id_partial (cost=0.00..8,356.99 rows=522,513 width=0) (actual rows= loops=)

20.          

SubPlan (for Subquery Scan)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.28..18.19 rows=1 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..16.91 rows=1 width=12) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using idx_file_associations_cid_ap_payee_id on file_associations fa (cost=0.43..8.45 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((cid = ah1.cid) AND (ap_payee_id = ah1.ap_payee_id))
24. 0.000 0.000 ↓ 0.0

Index Scan using pk_files on files f (cost=0.43..8.45 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((cid = ah1.cid) AND (id = fa.file_id))
25. 0.000 0.000 ↓ 0.0

Index Scan using pk_file_types on file_types ft (cost=0.42..0.85 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((cid = ah1.cid) AND (id = f.file_type_id))
  • Filter: ((system_code)::text = 'LW'::text)
26. 0.000 0.000 ↓ 0.0

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=0.43..8.46 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((cid = 9848) AND (ap_header_id = ah1.id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (ah1.gl_transaction_type_id = gl_transaction_type_id) AND (ah1.post_month = post_month))
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ap_details ad1 (cost=2,868.48..101,206.77 rows=151,232 width=12) (actual rows= loops=)

  • Recheck Cond: (cid = 9848)
28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..2,830.67 rows=151,232 width=0) (actual rows= loops=)

  • Index Cond: (cid = 9848)
29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.70..5,734.10 rows=2,898 width=21) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..5,726.86 rows=2,898 width=21) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Index Scan using pk_ap_payments on ap_payments ap (cost=0.43..5,682.34 rows=2,898 width=13) (actual rows= loops=)

  • Index Cond: (cid = 9848)
32. 0.000 0.000 ↓ 0.0

Materialize (cost=0.27..8.29 rows=1 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using idx_job_phases_job_id on job_phases jp (cost=0.27..8.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (job_id = 175)
  • Filter: (cid = 9848)
34. 0.000 0.000 ↓ 0.0

Index Scan using idx_ap_allocations_charge_ap_detail_id on ap_allocations aa (cost=0.43..1.34 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (charge_ap_detail_id = ad1.id)