explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CKjE : after removing order by

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 121,720.461 ↑ 1.0 25 1

Limit (cost=2,100,377.24..2,100,378.36 rows=25 width=300) (actual time=121,720.416..121,720.461 rows=25 loops=1)

2.          

CTE load_properties_info

3. 0.107 26.049 ↓ 395.0 395 1

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

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

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

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

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

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

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

7. 0.029 121,720.444 ↑ 5,636.2 25 1

Unique (cost=2,100,345.50..2,106,686.18 rows=140,904 width=300) (actual time=121,720.413..121,720.444 rows=25 loops=1)

8. 110.393 121,720.415 ↑ 2,935.5 48 1

Sort (cost=2,100,345.50..2,100,697.76 rows=140,904 width=300) (actual time=121,720.412..121,720.415 rows=48 loops=1)

  • Sort Key: ap.id, 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,301.749 121,610.022 ↓ 1.0 143,786 1

WindowAgg (cost=30,937.20..2,088,295.14 rows=140,904 width=300) (actual time=119,494.533..121,610.022 rows=143,786 loops=1)

10. 60.735 119,308.273 ↓ 1.0 143,786 1

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

11. 91.241 1,581.843 ↓ 1.0 140,917 1

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

  • Hash Cond: (ah.ap_payee_location_id = apl.id)
12. 123.704 1,403.812 ↓ 1.0 140,917 1

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

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

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

14. 65.453 213.527 ↓ 1.0 140,917 1

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

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

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

  • Hash Cond: (ap.ap_payment_type_id = apt.id)
16. 89.530 89.530 ↓ 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.011..89.530 rows=140,917 loops=1)

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

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

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

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

19. 0.103 0.275 ↑ 1.0 691 1

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

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

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

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

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

  • Group Key: ah_1.cid, ah_1.ap_payment_id
22. 563.668 563.668 ↑ 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.003..0.004 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. 101.252 314.780 ↑ 1.0 423,850 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 27276kB
24. 213.528 213.528 ↑ 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.006..213.528 rows=423,850 loops=1)

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

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

  • Buckets: 262144 Batches: 1 Memory Usage: 9704kB
26. 43.245 43.245 ↑ 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=0.016..43.245 rows=193,136 loops=1)

  • Filter: (cid = 3395)
  • Rows Removed by Filter: 1
27. 422.751 117,665.695 ↑ 1.0 1 140,917

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

  • Group Key: gd.gl_reconciliation_id, gr.gl_reconciliation_status_type_id
28. 281.834 117,242.944 ↓ 2.0 2 140,917

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

  • Sort Key: gd.gl_reconciliation_id, gr.gl_reconciliation_status_type_id
  • Sort Method: quicksort Memory: 25kB
29. 80.510 116,961.110 ↓ 2.0 2 140,917

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

  • One-Time Filter: ((ah.cid = 3395) AND (NOT ah.is_initial_import))
30. 231.010 116,880.600 ↓ 2.0 2 140,820

Nested Loop Left Join (cost=2.26..11.50 rows=1 width=130) (actual time=0.431..0.830 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. 90.190 115,190.760 ↓ 2.0 2 140,820

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

32. 284.112 113,641.740 ↓ 2.0 2 140,820

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

33. 0.000 112,796.820 ↓ 2.0 2 140,820

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

34. 5,914.440 5,914.440 ↓ 395.0 395 140,820

CTE Scan on load_properties_info lp (cost=0.00..0.02 rows=1 width=126) (actual time=0.000..0.042 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. 560.808 560.808 ↑ 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.002..0.002 rows=1 loops=280,404)

  • Index Cond: (credit_ap_detail_id = ad.id)
  • Filter: (cid = ad.cid)
37. 1,458.830 1,458.830 ↑ 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.004..0.005 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. 121.976 1,458.830 ↑ 1.0 1 291,766

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

39. 875.298 875.298 ↑ 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.003..0.003 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 : 7.486 ms