explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yxv8

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 260,601.294 ↑ 1.0 25 1

Limit (cost=2,100,377.24..2,100,378.36 rows=25 width=300) (actual time=260,601.260..260,601.294 rows=25 loops=1)

2.          

CTE load_properties_info

3. 0.169 356.507 ↓ 395.0 395 1

Hash Join (cost=1.50..31.74 rows=1 width=27) (actual time=356.136..356.507 rows=395 loops=1)

  • Hash Cond: ((p.cid = lp_1.cid) AND (p.id = lp_1.property_id))
4. 0.905 0.905 ↑ 1.0 402 1

Seq Scan on properties p (cost=0.00..27.21 rows=402 width=27) (actual time=0.674..0.905 rows=402 loops=1)

  • Filter: (is_disabled = 0)
  • Rows Removed by Filter: 255
5. 0.054 355.433 ↓ 7.9 395 1

Hash (cost=0.75..0.75 rows=50 width=8) (actual time=355.433..355.433 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
6. 355.379 355.379 ↓ 7.9 395 1

Function Scan on load_properties lp_1 (cost=0.25..0.75 rows=50 width=8) (actual time=355.344..355.379 rows=395 loops=1)

7. 0.017 260,601.277 ↑ 5,636.2 25 1

Unique (cost=2,100,345.50..2,106,686.18 rows=140,904 width=300) (actual time=260,601.259..260,601.277 rows=25 loops=1)

8. 121.026 260,601.260 ↑ 5,636.2 25 1

Sort (cost=2,100,345.50..2,100,697.76 rows=140,904 width=300) (actual time=260,601.258..260,601.260 rows=25 loops=1)

  • Sort Key: ap.id DESC, ap.payment_status_type_id, ap.ap_payment_type_id, ap.bank_account_id, ap.payment_date, ap.payment_amount, (func_format_refund_customer_names(ap.payee_name, false)), ap.payment_number, apt.name, ba.account_name, ah.id, ah.post_month, (CASE WHEN ah.is_initial_import THEN 1 ELSE 0 END), apl.vendor_code, (CASE WHEN (1 < (count(DISTINCT ad.property_id))) THEN 'Multiple'::text ELSE (max((lp.property_name)::text)) END), (CASE WHEN (ap.ap_payment_type_id = ANY ('{8,9}'::integer[])) THEN CASE WHEN ((gd.gl_reconciliation_id IS NOT NULL) AND (gr.gl_reconciliation_status_type_id = 3)) THEN 'Cleared'::text WHEN ((gd.gl_reconciliation_id IS NOT NULL) AND (gr.gl_reconciliation_status_type_id = 1)) THEN 'Reconciled'::text WHEN (ap.payment_status_type_id = 1) THEN 'Pending'::text WHEN (ap.payment_status_type_id = 2) THEN 'Received [Unreconciled]'::text WHEN (ap.payment_status_type_id = 7) THEN 'Voiding'::text WHEN (ap.payment_status_type_id = 9) THEN 'Capturing'::text WHEN (ap.payment_status_type_id = 10) THEN 'Captured'::text WHEN (ap.payment_status_type_id = 8) THEN 'Voided'::text WHEN (ap.payment_status_type_id = ANY ('{15,30}'::integer[])) THEN 'Returned'::text ELSE NULL::text END WHEN (ap.payment_status_type_id = 8) THEN 'Voided'::text WHEN (ap.payment_status_type_id = ANY ('{15,30}'::integer[])) THEN 'Returned'::text ELSE CASE WHEN ((gd.gl_reconciliation_id IS NOT NULL) AND (gr.gl_reconciliation_status_type_id = 3)) THEN 'Cleared'::text WHEN (gd.gl_reconciliation_id IS NULL) THEN 'Unreconciled'::text ELSE 'Reconciled'::text END END), (count(ap.id) OVER (?))
  • Sort Method: quicksort Memory: 44055kB
9. 2,767.839 260,480.234 ↓ 1.0 143,786 1

WindowAgg (cost=30,937.20..2,088,295.14 rows=140,904 width=300) (actual time=257,975.968..260,480.234 rows=143,786 loops=1)

10. 144.159 257,712.395 ↓ 1.0 143,786 1

Nested Loop (cost=30,937.20..2,046,023.94 rows=140,904 width=260) (actual time=1,259.361..257,712.395 rows=143,786 loops=1)

11. 122.750 2,508.466 ↓ 1.0 140,917 1

Hash Join (cost=30,925.69..417,347.51 rows=140,904 width=224) (actual time=889.261..2,508.466 rows=140,917 loops=1)

  • Hash Cond: (ah.ap_payee_location_id = apl.id)
12. 151.226 2,248.832 ↓ 1.0 140,917 1

Hash Join (cost=23,295.26..409,347.20 rows=140,904 width=221) (actual time=752.080..2,248.832 rows=140,917 loops=1)

  • Hash Cond: ((max(LEAST(ah_1.id, ah_1.reversal_ap_header_id))) = ah.id)
13. 162.213 1,351.782 ↓ 1.0 140,917 1

Nested Loop (cost=49.01..385,731.08 rows=140,904 width=204) (actual time=5.636..1,351.782 rows=140,917 loops=1)

14. 90.668 344.067 ↓ 1.0 140,917 1

Hash Join (cost=48.59..7,051.58 rows=140,904 width=200) (actual time=5.607..344.067 rows=140,917 loops=1)

  • Hash Cond: (ap.bank_account_id = ba.id)
15. 95.485 249.011 ↓ 1.0 140,917 1

Hash Join (cost=1.32..6,632.16 rows=140,904 width=172) (actual time=1.212..249.011 rows=140,917 loops=1)

  • Hash Cond: (ap.ap_payment_type_id = apt.id)
16. 153.158 153.158 ↓ 1.0 140,917 1

Seq Scan on ap_payments ap (cost=0.00..6,149.00 rows=140,904 width=54) (actual time=0.830..153.158 rows=140,917 loops=1)

  • Filter: ((NOT is_unclaimed_property) AND (cid = 3395))
  • Rows Removed by Filter: 43
17. 0.004 0.368 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=122) (actual time=0.367..0.368 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.364 0.364 ↑ 1.0 14 1

Seq Scan on ap_payment_types apt (cost=0.00..1.14 rows=14 width=122) (actual time=0.362..0.364 rows=14 loops=1)

19. 0.174 4.388 ↑ 1.0 691 1

Hash (cost=38.64..38.64 rows=691 width=36) (actual time=4.388..4.388 rows=691 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
20. 4.214 4.214 ↑ 1.0 691 1

Seq Scan on bank_accounts ba (cost=0.00..38.64 rows=691 width=36) (actual time=0.793..4.214 rows=691 loops=1)

  • Filter: (cid = 3395)
21. 140.917 845.502 ↑ 1.0 1 140,917

GroupAggregate (cost=0.42..2.67 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=140,917)

  • Group Key: ah_1.cid, ah_1.ap_payment_id
22. 704.585 704.585 ↑ 1.0 1 140,917

Index Scan using idx_ap_headers_ap_payment_id on ap_headers ah_1 (cost=0.42..2.65 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=140,917)

  • Index Cond: (ap_payment_id = ap.id)
  • Filter: ((deleted_on IS NULL) AND (cid = ap.cid) AND (ap_header_type_id = 5) AND (gl_transaction_type_id = 3))
23. 120.263 745.824 ↑ 1.0 423,850 1

Hash (cost=17,948.12..17,948.12 rows=423,850 width=21) (actual time=745.824..745.824 rows=423,850 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 27276kB
24. 625.561 625.561 ↑ 1.0 423,850 1

Seq Scan on ap_headers ah (cost=0.00..17,948.12 rows=423,850 width=21) (actual time=0.012..625.561 rows=423,850 loops=1)

  • Filter: ((deleted_on IS NULL) AND (cid = 3395))
25. 41.481 136.884 ↑ 1.0 193,136 1

Hash (cost=5,216.21..5,216.21 rows=193,137 width=15) (actual time=136.884..136.884 rows=193,136 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9704kB
26. 95.403 95.403 ↑ 1.0 193,136 1

Seq Scan on ap_payee_locations apl (cost=0.00..5,216.21 rows=193,137 width=15) (actual time=1.201..95.403 rows=193,136 loops=1)

  • Filter: (cid = 3395)
  • Rows Removed by Filter: 1
27. 563.668 255,059.770 ↑ 1.0 1 140,917

GroupAggregate (cost=11.51..11.54 rows=1 width=48) (actual time=1.810..1.810 rows=1 loops=140,917)

  • Group Key: gd.gl_reconciliation_id, gr.gl_reconciliation_status_type_id
28. 422.751 254,496.102 ↓ 2.0 2 140,917

Sort (cost=11.51..11.52 rows=1 width=130) (actual time=1.805..1.806 rows=2 loops=140,917)

  • Sort Key: gd.gl_reconciliation_id, gr.gl_reconciliation_status_type_id
  • Sort Method: quicksort Memory: 25kB
29. 34.071 254,073.351 ↓ 2.0 2 140,917

Result (cost=2.26..11.50 rows=1 width=130) (actual time=0.985..1.803 rows=2 loops=140,917)

  • One-Time Filter: ((ah.cid = 3395) AND (NOT ah.is_initial_import))
30. 269.626 254,039.280 ↓ 2.0 2 140,820

Nested Loop Left Join (cost=2.26..11.50 rows=1 width=130) (actual time=0.986..1.804 rows=2 loops=140,820)

  • Join Filter: ((gd.amount = CASE WHEN ((aa.origin_ap_allocation_id IS NOT NULL) OR (aa.lump_ap_header_id IS NOT NULL)) THEN (aa.allocation_amount * '-1'::numeric) ELSE aa.allocation_amount END) AND (gd.property_id = CASE WHEN (ba.reimbursed_property_id <> aa.property_id) THEN ba.reimbursed_property_id ELSE aa.property_id END))
  • Rows Removed by Join Filter: 0
31. 347.286 245,308.440 ↓ 2.0 2 140,820

Nested Loop Left Join (cost=1.41..7.50 rows=1 width=148) (actual time=0.943..1.742 rows=2 loops=140,820)

32. 286.584 137,299.500 ↓ 2.0 2 140,820

Nested Loop Left Join (cost=0.85..5.34 rows=1 width=152) (actual time=0.502..0.975 rows=2 loops=140,820)

33. 17,602.500 135,891.300 ↓ 2.0 2 140,820

Nested Loop (cost=0.42..2.68 rows=1 width=130) (actual time=0.497..0.965 rows=2 loops=140,820)

34. 7,041.000 7,041.000 ↓ 395.0 395 140,820

CTE Scan on load_properties_info lp (cost=0.00..0.02 rows=1 width=126) (actual time=0.003..0.050 rows=395 loops=140,820)

35. 111,247.800 111,247.800 ↓ 0.0 0 55,623,900

Index Scan using idx_ap_details_cid_property_id_ap_header_id on ap_details ad (cost=0.42..2.65 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=55,623,900)

  • Index Cond: ((cid = lp.cid) AND (property_id = lp.property_id) AND (ah.id = ap_header_id))
  • Filter: ((deleted_on IS NULL) AND (ah.gl_transaction_type_id = gl_transaction_type_id) AND (ah.post_month = post_month))
36. 1,121.616 1,121.616 ↑ 1.0 1 280,404

Index Scan using idx_ap_allocations_credit_ap_detail_id on ap_allocations aa (cost=0.42..2.64 rows=1 width=34) (actual time=0.003..0.004 rows=1 loops=280,404)

  • Index Cond: (credit_ap_detail_id = ad.id)
  • Filter: (cid = ad.cid)
37. 107,661.654 107,661.654 ↑ 1.0 1 291,766

Index Scan using idx_gl_headers_cid_reference_id on gl_headers gh (cost=0.56..2.15 rows=1 width=16) (actual time=0.356..0.369 rows=1 loops=291,766)

  • Index Cond: ((cid = aa.cid) AND (reference_id = aa.id))
  • Filter: ((gl_transaction_type_id = 4) AND (gl_transaction_type_id = aa.gl_transaction_type_id))
  • Rows Removed by Filter: 1
38. 413.742 8,461.214 ↑ 1.0 1 291,766

Nested Loop Left Join (cost=0.85..3.99 rows=1 width=25) (actual time=0.028..0.029 rows=1 loops=291,766)

39. 7,585.916 7,585.916 ↑ 1.0 1 291,766

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.57..3.68 rows=1 width=21) (actual time=0.026..0.026 rows=1 loops=291,766)

  • Index Cond: (gl_header_id = gh.id)
  • Filter: ((gl_reconciliation_id IS NOT NULL) AND (cid = gh.cid))
  • Rows Removed by Filter: 1
40. 461.556 461.556 ↑ 1.0 1 230,778

Index Scan using pk_gl_reconciliations on gl_reconciliations gr (cost=0.28..0.30 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=230,778)

  • Index Cond: ((cid = gd.cid) AND (id = gd.gl_reconciliation_id))
Planning time : 288.508 ms