explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7GJu

Settings
# exclusive inclusive rows x rows loops node
1. 44.222 4,548.512 ↓ 32.3 6,267 1

Unique (cost=5,072.25..5,081.95 rows=194 width=198) (actual time=4,500.740..4,548.512 rows=6,267 loops=1)

2. 281.200 4,504.290 ↓ 233.8 45,352 1

Sort (cost=5,072.25..5,072.74 rows=194 width=198) (actual time=4,500.735..4,504.290 rows=45,352 loops=1)

  • Sort Key: ap.id, (CASE WHEN ((ad_charge.reimbursement_ap_detail_id IS NOT NULL) OR ad_charge.is_reimbursement) THEN 'true'::text ELSE 'false'::text END), ba.account_name, ap.bank_account_id, apt.name, apt.id, ap.payment_date, ap.payment_amount, (func_format_refund_customer_names(ap.payee_name, false)), ap.payment_number, ah.post_month, ah.id, (CASE WHEN ah.is_initial_import THEN 1 ELSE 0 END), prop_count.property_count, (max(ad_charge.property_id) OVER (?)), ap.payment_status_type_id, apl.vendor_code, (CASE WHEN ((ap.ap_payment_type_id = 8) OR (ap.ap_payment_type_id = 9)) 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), gr.gl_reconciliation_status_type_id
  • Sort Method: quicksort Memory: 13591kB
3. 2,005.444 4,223.090 ↓ 233.8 45,352 1

WindowAgg (cost=5,005.71..5,064.88 rows=194 width=198) (actual time=2,208.220..4,223.090 rows=45,352 loops=1)

4. 73.097 2,217.646 ↓ 233.8 45,352 1

Sort (cost=5,005.71..5,006.19 rows=194 width=129) (actual time=2,205.926..2,217.646 rows=45,352 loops=1)

  • Sort Key: ah1.ap_payment_id
  • Sort Method: quicksort Memory: 13433kB
5. 32.997 2,144.549 ↓ 233.8 45,352 1

Nested Loop Left Join (cost=2,438.20..4,998.34 rows=194 width=129) (actual time=23.241..2,144.549 rows=45,352 loops=1)

6. 129.746 2,066.200 ↓ 233.8 45,352 1

Nested Loop Left Join (cost=2,437.92..4,939.60 rows=194 width=129) (actual time=23.231..2,066.200 rows=45,352 loops=1)

  • 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: 38104
7. 49.268 1,618.990 ↓ 233.8 45,352 1

Nested Loop Left Join (cost=2,437.49..4,466.16 rows=194 width=151) (actual time=23.150..1,618.990 rows=45,352 loops=1)

8. 70.986 1,252.258 ↓ 233.8 45,352 1

Nested Loop Left Join (cost=2,437.06..4,127.91 rows=194 width=155) (actual time=23.094..1,252.258 rows=45,352 loops=1)

9. 58.428 954.512 ↓ 233.8 45,352 1

Nested Loop Left Join (cost=2,436.63..3,975.75 rows=194 width=150) (actual time=23.038..954.512 rows=45,352 loops=1)

10. 42.436 663.896 ↓ 199.5 38,698 1

Nested Loop Left Join (cost=2,436.21..3,877.99 rows=194 width=124) (actual time=22.990..663.896 rows=38,698 loops=1)

11. 65.068 466.668 ↓ 199.5 38,698 1

Nested Loop Left Join (cost=2,435.79..3,774.71 rows=194 width=122) (actual time=22.957..466.668 rows=38,698 loops=1)

12. 34.818 285.506 ↓ 199.5 38,698 1

Hash Left Join (cost=2,435.52..3,711.41 rows=194 width=104) (actual time=22.921..285.506 rows=38,698 loops=1)

  • Hash Cond: (ap.ap_payment_type_id = apt.id)
13. 47.349 250.658 ↓ 199.5 38,698 1

Nested Loop Left Join (cost=2,433.20..3,708.43 rows=194 width=89) (actual time=22.856..250.658 rows=38,698 loops=1)

  • Filter: CASE WHEN (ah.post_month IS NULL) THEN (ad.property_id IS NULL) ELSE (ad.property_id = ANY ('{225227,225228,225236,225242,225254,225256,225260,225329,225745,225748,225755,225758,226452,226922,227616,229303,230590,231341,231344,232165,232177,232188,233227,235474,236963,238400,239000,241013,241533,242803,242805,242809,242812,242823,243905,246489,249035,249056,249071,249091,249095,249219,250608,252323,252459,253123,253130,253239,253242,253620,253695,253709,253951,254382,254433,254524,254692,254695,254696,254697,254698,254699,254742,254896,257620,257693,257851,258505,258615,258628,258631,258633,258635,258638,258643,259122,259168,259609,259828,259832,259836,259841,260091,260370,261760,262499,262503,262517,263638,264628,264630,264633,264648,267238,267250,267924,267962,267987,267988,267993,267999,268095,268278,268279,268465,268568,268588,268591,268592,268677,269158,269159,269161,269266,269273,269276,269278,269279,269478,269486,269488,269491,296861,296995,296996,296999,324525,324530,324531,324535,324540,325021,325023,325026,325079,325294,325479,325481,325884,325886,325896,325902,326325,326326,326332,326333,326353,326481,326486,326516,327017,328609,328626,328634,329228,329273,329350,329446,329494,329566,329575,329576,329577,329578,329579,329580,329647,329648,329755,329757,330642,330677,331555,331557,331558,331559,331561,331562,331563,331565,331870,331872,331875,334371,334382,334388,334390,337034,337072,337112,337115,337128,348840,348858,351334,351342,351447,351548,351549,352359,352487,352490,352797,352798,352799,353118,353597,353600,353606,353607,353608,353621,357465,359486,359487,359488,359516,359617,359618,359619,359636,359747,482524,483398,484365,484366,484367,484383,484394,484395,484396,484400,486200,486215,486218,488393,488407,488408,488409,489610,489807,490262,490263,490264,490638,491249,491254,493174,493175,493176,493177,493178,493179,494340,494347,496520,496522,496524,496526,496550,496808,496809,496810,496813,497222,497232,497287,497432,497438,497616,497618,497620,497861,497864,502805,503574,504241,504523,504533,504640,504663,504764,505150,505151,505191,505192,505193,505200,505285,505484,505485,505486,505795,505862,507149,507150,508081,508107,508184,508187,508190,508338,508339,508340,508499,508593,511154,511155,511159,511219,511250,511404,511729,511730,511731,512075,512314,513968,514295,515017,515829,516367,517436,518976,519080,519233,519258,519342,519399,519603,520621,520639,521776,522241,522433,523272,523280,523307,524914,524968,524970,525632,527220,527221,527222,528522,528536,529304,529315,529351,530740,530748,530749,530750,531307,531351,531352,532979,532993,533135,533136,533137,533229,536078,538934,539023,539073,540275,542053,545838,545851,545852,545853,545898,546757,550500,550553,550704,551060,551078,553566,553710,553711,553895,556037,556038,556461,556465,556466,556467,557885,557887,557892,557900,558033,560687,560691,560692,560693,560912,560915,560929,560940,560944,564100,571996,571997,571998,572006,572007,572008,572009,572010,572271,572775,572776,572777,573345,576861,577833,577863,577867,577868,577869,577871,577872,577886,577922,577926,577927,577928,577965,578459,578466,578753,580717,580718,580719,580730,582360,582827,584285,584466,584467,584629,584630,584631,584693,584694,584705,584706,584940,584946,584947,586785,587518,589865,589866,589867,589888,589974,589975,589976,589977,591571,593584,593765,593957,593969,593997,598121,598125,599150,599210,599254,599255,599256,599257,599487,601794,601819,601821,601822,602120,603198,603224,605297,605344,608386,609827,609828,613188,613191,614450,618893,618930,621538,621540,622819,626634,626638,628718,628778,628780,628782,628858,628921,628922,628923,628927,628929,630294,630377,630414,630416,630548,631004,631034,631121,633038,633039,633153,637751,637763,639434,639442,640348,640350,640748,640965,640967,642284,643276,643479,645904,645906,646255,647051,647053,647077,647079,647098,647103,647108,647112,647121,647124,649214,649275,650526,650528,653627,653628,662357,667756,671038,676391,680096,687243,688819,691469,695032,695036,695110}'::integer[])) END
  • Rows Removed by Filter: 1
14. 10.880 95.813 ↓ 15.4 5,972 1

Nested Loop Left Join (cost=2,432.78..3,062.25 rows=387 width=85) (actual time=22.825..95.813 rows=5,972 loops=1)

15. 10.518 37.157 ↓ 15.4 5,972 1

Hash Right Join (cost=2,432.36..2,586.35 rows=387 width=72) (actual time=22.783..37.157 rows=5,972 loops=1)

  • Hash Cond: ((ah1.cid = ap.cid) AND (ah1.ap_payment_id = ap.id))
16. 3.892 3.892 ↑ 1.0 6,033 1

Seq Scan on ah1 (cost=0.00..108.41 rows=6,033 width=12) (actual time=0.014..3.892 rows=6,033 loops=1)

  • Filter: (cid = 12975)
17. 2.624 22.747 ↓ 15.4 5,972 1

Hash (cost=2,426.55..2,426.55 rows=387 width=60) (actual time=22.747..22.747 rows=5,972 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 615kB
18. 3.214 20.123 ↓ 15.4 5,972 1

Hash Join (cost=190.12..2,426.55 rows=387 width=60) (actual time=2.485..20.123 rows=5,972 loops=1)

  • Hash Cond: (ap.id = prop_count.ap_payment_id)
19. 14.529 14.529 ↓ 1.3 5,972 1

Index Scan using pk_ap_payments on ap_payments ap (cost=0.29..2,215.86 rows=4,533 width=52) (actual time=0.061..14.529 rows=5,972 loops=1)

  • Index Cond: (cid = 12975)
  • Filter: ((NOT is_unclaimed_property) AND (ap_payment_type_id = ANY ('{6,1,2,4,3,7,5,8,9,100,10,11,12,13,14}'::integer[])))
  • Rows Removed by Filter: 61
20. 1.239 2.380 ↑ 1.0 6,033 1

Hash (cost=114.41..114.41 rows=6,033 width=16) (actual time=2.380..2.380 rows=6,033 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 347kB
21. 1.141 1.141 ↑ 1.0 6,033 1

Seq Scan on prop_count (cost=0.00..114.41 rows=6,033 width=16) (actual time=0.013..1.141 rows=6,033 loops=1)

  • Filter: (cid = 12975)
22. 47.776 47.776 ↑ 1.0 1 5,972

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..1.22 rows=1 width=25) (actual time=0.008..0.008 rows=1 loops=5,972)

  • Index Cond: (id = ah1.id)
  • Filter: ((cid = 12975) AND (cid = ah1.cid))
  • Rows Removed by Filter: 0
23. 107.496 107.496 ↓ 6.0 6 5,972

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=0.42..0.95 rows=1 width=24) (actual time=0.008..0.018 rows=6 loops=5,972)

  • Index Cond: ((ah.cid = cid) AND (cid = 12975) AND (ah.id = ap_header_id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (ah.gl_transaction_type_id = gl_transaction_type_id) AND (ah.post_month = post_month))
  • Rows Removed by Filter: 0
24. 0.007 0.030 ↑ 1.0 14 1

Hash (cost=2.14..2.14 rows=14 width=15) (actual time=0.030..0.030 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.023 0.023 ↑ 1.0 14 1

Seq Scan on ap_payment_types apt (cost=0.00..2.14 rows=14 width=15) (actual time=0.006..0.023 rows=14 loops=1)

26. 116.094 116.094 ↑ 1.0 1 38,698

Index Scan using idx_bank_accounts on bank_accounts ba (cost=0.28..0.32 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=38,698)

  • Index Cond: (id = ap.bank_account_id)
  • Filter: ((cid = 12975) AND (cid = ap.cid))
27. 154.792 154.792 ↑ 1.0 1 38,698

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.42..0.52 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=38,698)

  • Index Cond: (id = ah.ap_payee_location_id)
  • Filter: ((cid = 12975) AND (cid = ah.cid))
28. 232.188 232.188 ↑ 1.0 1 38,698

Index Scan using idx_ap_allocations_credit_ap_detail_id on ap_allocations aa (cost=0.42..0.49 rows=1 width=38) (actual time=0.005..0.006 rows=1 loops=38,698)

  • Index Cond: (credit_ap_detail_id = ad.id)
  • Filter: ((cid = 12975) AND (cid = ad.cid))
29. 226.760 226.760 ↑ 1.0 1 45,352

Index Scan using idx_ap_details on ap_details ad_charge (cost=0.42..0.77 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=45,352)

  • Index Cond: (id = aa.charge_ap_detail_id)
  • Filter: ((cid = 12975) AND (cid = aa.cid))
30. 317.464 317.464 ↑ 1.0 1 45,352

Index Scan using idx_gl_headers_cid_reference_id on gl_headers gh (cost=0.43..1.73 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=45,352)

  • Index Cond: ((cid = aa.cid) AND (cid = 12975) 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: 0
31. 317.464 317.464 ↓ 2.0 2 45,352

Index Scan using idx_gl_details_cid_gl_header_id on gl_details gd (cost=0.43..2.42 rows=1 width=21) (actual time=0.006..0.007 rows=2 loops=45,352)

  • Index Cond: ((cid = gh.cid) AND (cid = 12975) AND (gl_header_id = gh.id))
32. 45.352 45.352 ↓ 0.0 0 45,352

Index Scan using pk_gl_reconciliations on gl_reconciliations gr (cost=0.28..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=45,352)

  • Index Cond: ((cid = gd.cid) AND (cid = 12975) AND (id = gd.gl_reconciliation_id))