explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BqBw

Settings
# exclusive inclusive rows x rows loops node
1. 44.068 92,551.739 ↓ 2.8 6,267 1

Unique (cost=20,392.84..20,506.14 rows=2,266 width=198) (actual time=92,504.692..92,551.739 rows=6,267 loops=1)

2. 305.861 92,507.671 ↓ 20.0 45,352 1

Sort (cost=20,392.84..20,398.51 rows=2,266 width=198) (actual time=92,504.689..92,507.671 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), ((SubPlan 1)), (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,498.504 92,201.810 ↓ 20.0 45,352 1

WindowAgg (cost=14,012.40..20,266.56 rows=2,266 width=198) (actual time=1,527.297..92,201.810 rows=45,352 loops=1)

4. 64.975 1,539.018 ↓ 20.0 45,352 1

Sort (cost=14,012.40..14,018.06 rows=2,266 width=125) (actual time=1,524.914..1,539.018 rows=45,352 loops=1)

  • Sort Key: ah1.ap_payment_id
  • Sort Method: quicksort Memory: 13188kB
5. 27.373 1,474.043 ↓ 20.0 45,352 1

Hash Left Join (cost=3,836.34..13,886.11 rows=2,266 width=125) (actual time=652.771..1,474.043 rows=45,352 loops=1)

  • Hash Cond: ((gd.cid = gr.cid) AND (gd.gl_reconciliation_id = gr.id))
6. 75.334 1,446.162 ↓ 20.0 45,352 1

Nested Loop Left Join (cost=3,762.61..13,800.49 rows=2,266 width=125) (actual time=652.237..1,446.162 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. 88.483 1,098.716 ↓ 20.0 45,352 1

Nested Loop Left Join (cost=3,762.18..8,272.18 rows=2,266 width=147) (actual time=652.182..1,098.716 rows=45,352 loops=1)

8. 82.491 828.825 ↓ 20.0 45,352 1

Hash Right Join (cost=3,761.76..6,518.39 rows=2,266 width=146) (actual time=652.134..828.825 rows=45,352 loops=1)

  • Hash Cond: ((gh.cid = aa.cid) AND (gh.reference_id = aa.id) AND (gh.gl_transaction_type_id = aa.gl_transaction_type_id))
9. 94.293 94.293 ↓ 14.8 40,047 1

Index Scan using idx_gl_headers_cid_gl_transaction_type_id_post_month_reference_ on gl_headers gh (cost=0.43..2,716.60 rows=2,697 width=16) (actual time=0.063..94.293 rows=40,047 loops=1)

  • Index Cond: ((cid = 12975) AND (gl_transaction_type_id = 4))
10. 47.409 652.041 ↓ 20.0 45,352 1

Hash (cost=3,721.67..3,721.67 rows=2,266 width=146) (actual time=652.041..652.041 rows=45,352 loops=1)

  • Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 8750kB
11. 56.859 604.632 ↓ 20.0 45,352 1

Nested Loop Left Join (cost=331.87..3,721.67 rows=2,266 width=146) (actual time=290.716..604.632 rows=45,352 loops=1)

12. 20.036 392.981 ↓ 17.1 38,698 1

Hash Left Join (cost=331.45..2,579.62 rows=2,266 width=120) (actual time=290.681..392.981 rows=38,698 loops=1)

  • Hash Cond: ((ap.cid = ba.cid) AND (ap.bank_account_id = ba.id))
13. 16.989 372.206 ↓ 17.1 38,698 1

Hash Left Join (cost=254.91..2,491.16 rows=2,266 width=102) (actual time=289.933..372.206 rows=38,698 loops=1)

  • Hash Cond: (ap.ap_payment_type_id = apt.id)
14. 42.078 355.187 ↓ 17.1 38,698 1

Merge Left Join (cost=252.59..2,481.10 rows=2,266 width=87) (actual time=289.890..355.187 rows=38,698 loops=1)

  • Merge Cond: (ap.id = ah1.ap_payment_id)
  • Join Filter: (ap.cid = ah1.cid)
  • 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
15. 15.697 15.697 ↓ 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.069..15.697 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
16. 29.180 297.412 ↓ 1,140.6 38,782 1

Sort (cost=252.30..252.38 rows=34 width=43) (actual time=289.807..297.412 rows=38,782 loops=1)

  • Sort Key: ah1.ap_payment_id
  • Sort Method: quicksort Memory: 4575kB
17. 9.680 268.232 ↓ 1,140.6 38,782 1

Nested Loop Left Join (cost=1.26..251.43 rows=34 width=43) (actual time=0.131..268.232 rows=38,782 loops=1)

18. 11.703 142.206 ↓ 1,140.6 38,782 1

Nested Loop Left Join (cost=0.84..233.33 rows=34 width=37) (actual time=0.093..142.206 rows=38,782 loops=1)

19. 8.173 40.008 ↓ 177.4 6,033 1

Nested Loop Left Join (cost=0.42..200.53 rows=34 width=29) (actual time=0.047..40.008 rows=6,033 loops=1)

20. 1.670 1.670 ↓ 177.4 6,033 1

Seq Scan on ah1 (cost=0.00..117.15 rows=34 width=12) (actual time=0.012..1.670 rows=6,033 loops=1)

  • Filter: (cid = 12975)
21. 30.165 30.165 ↑ 1.0 1 6,033

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..2.44 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=6,033)

  • Index Cond: (id = ah1.id)
  • Filter: ((cid = 12975) AND (cid = ah1.cid))
  • Rows Removed by Filter: 0
22. 90.495 90.495 ↓ 6.0 6 6,033

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=0.42..0.95 rows=1 width=24) (actual time=0.007..0.015 rows=6 loops=6,033)

  • 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
23. 116.346 116.346 ↑ 1.0 1 38,782

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.003..0.003 rows=1 loops=38,782)

  • Index Cond: (id = ah.ap_payee_location_id)
  • Filter: ((cid = 12975) AND (cid = ah.cid))
24. 0.006 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.024 0.024 ↑ 1.0 14 1

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

26. 0.123 0.739 ↑ 1.0 385 1

Hash (cost=70.77..70.77 rows=385 width=26) (actual time=0.738..0.739 rows=385 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
27. 0.616 0.616 ↑ 1.0 385 1

Index Scan using pk_bank_accounts on bank_accounts ba (cost=0.28..70.77 rows=385 width=26) (actual time=0.045..0.616 rows=385 loops=1)

  • Index Cond: (cid = 12975)
28. 154.792 154.792 ↑ 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.004..0.004 rows=1 loops=38,698)

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

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

  • Index Cond: ((cid = aa.cid) AND (cid = 12975) AND (id = aa.charge_ap_detail_id))
30. 272.112 272.112 ↓ 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.004..0.006 rows=2 loops=45,352)

  • Index Cond: ((cid = gh.cid) AND (cid = 12975) AND (gl_header_id = gh.id))
31. 0.096 0.508 ↓ 1.0 303 1

Hash (cost=69.19..69.19 rows=302 width=12) (actual time=0.508..0.508 rows=303 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
32. 0.412 0.412 ↓ 1.0 303 1

Index Scan using pk_gl_reconciliations on gl_reconciliations gr (cost=0.28..69.19 rows=302 width=12) (actual time=0.025..0.412 rows=303 loops=1)

  • Index Cond: (cid = 12975)
33.          

SubPlan (forWindowAgg)

34. 12,108.984 88,164.288 ↑ 1.0 1 45,352

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=1.944..1.944 rows=1 loops=45,352)

35. 10,113.496 76,055.304 ↓ 1,513.0 1,513 45,352

Result (cost=0.42..2.44 rows=1 width=4) (actual time=0.014..1.677 rows=1,513 loops=45,352)

  • One-Time Filter: (ah.cid = 12975)
36. 65,941.808 65,941.808 ↓ 1,513.0 1,513 45,352

Index Scan using idx_ap_details_cid_apheaderid on ap_details (cost=0.42..2.44 rows=1 width=4) (actual time=0.013..1.454 rows=1,513 loops=45,352)

  • Index Cond: ((cid = 12975) AND (ap_header_id = ah.id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))