explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gZYx

Settings
# exclusive inclusive rows x rows loops node
1. 0.279 848.754 ↓ 239.0 239 1

Group (cost=229,956.69..229,956.73 rows=1 width=208) (actual time=848.463..848.754 rows=239 loops=1)

  • Group Key: j.id, j.cid, p.property_name, js.name, js.order_num, ah1.id, ah1.cid, (sum(ad.transaction_amount)), (sum(gd.amount))
2. 0.418 848.475 ↓ 239.0 239 1

Sort (cost=229,956.69..229,956.70 rows=1 width=144) (actual time=848.454..848.475 rows=239 loops=1)

  • Sort Key: j.id, p.property_name, js.name, js.order_num, ah1.id, (sum(ad.transaction_amount)), (sum(gd.amount))
  • Sort Method: quicksort Memory: 60kB
3. 3.055 848.057 ↓ 239.0 239 1

Nested Loop Left Join (cost=229,482.78..229,956.68 rows=1 width=144) (actual time=662.492..848.057 rows=239 loops=1)

  • Join Filter: ((j.cid = gd.cid) AND (j.id = jp_1.job_id))
  • Rows Removed by Join Filter: 19349
4. 0.307 189.664 ↓ 239.0 239 1

Nested Loop (cost=4,462.39..4,936.15 rows=1 width=112) (actual time=50.671..189.664 rows=239 loops=1)

5. 3.872 188.401 ↓ 239.0 239 1

Nested Loop Left Join (cost=4,462.26..4,935.99 rows=1 width=104) (actual time=50.646..188.401 rows=239 loops=1)

  • Join Filter: ((j.cid = jp.cid) AND (j.id = jp.job_id))
  • Rows Removed by Join Filter: 22384
6. 0.539 6.474 ↓ 239.0 239 1

Nested Loop (cost=1.60..475.27 rows=1 width=72) (actual time=0.570..6.474 rows=239 loops=1)

7. 0.378 3.067 ↓ 12.6 239 1

Merge Join (cost=1.18..432.86 rows=19 width=63) (actual time=0.507..3.067 rows=239 loops=1)

  • Merge Cond: (j.property_id = p.id)
8. 1.673 1.673 ↑ 1.2 239 1

Index Scan using idx_jobs_property_id on jobs j (cost=0.28..250.55 rows=289 width=52) (actual time=0.332..1.673 rows=239 loops=1)

  • Index Cond: (property_id = ANY ('{221310,508329,221633,267245,228019,497284,482784,706885,489606,646252,646253,497139,497141,639446,642198,643545,360331,505497,518458,561019,636923,531671,608450,649381,358396,353834,482765,531160,441764,582333,482521,584376,584377,589967,497392,642199,591134,631129,630370,650801,591129,482783,639460,588061,591127,591126,515947,649173,577855,505867,561802,626602,558030,577925,626604,558012,558010,626603,558013,515948,508508,558008,557922,618899,528514,560702,550627,570937,570936,570933,618898,575836,580741,531161,580931,521742,531166,667836,546011,508506,441765,556446,527242,354221,557944,557941,557939,524402,557923,518456,557935,482881,518062,557932,502205,527235,557930,511277,511276,511275,530767,564122,526857,526858,526859,526856,530768,503657,489095,489096,491015,491016,491017,441763,489094,441794,354364,441795,354363,354362,353836,353835,482523,482764,483200,483201,482766,505866,504655,504662,508330,630436,505496,505495,503655,507836,530770,676491,557924,591607,556394,540277,672245,508327,490662,531369,531167,673176,502604,673194,688489,676401,676399,483202,357493,696248,672248,681334,642247,614413,672255,696256,508507,653401,527248,614443,503656,591587,493189,493188,527244,497391,497390,497395,497394,497393,608438,608437,506942,502560,497361,608436,608435,489605,489604,489599,496602,486213,489603,690723,618900,503443,491869,520062,520059,504532,556391,561810,491751,502206,688658,489873,489871,529295,483858,576863,490639,696272,490591,682738,676370,637075,491070,498193,359601,685214,483396,614446,441793,484577,614445,614444,665387,679952,665386,483206,360587,509343,627076,495651,494366,494365,494364,491743,483196,486777,669830,669829,518141,360332,509163,356924,356923,356922,357452,518876,240844,577918,639149,526855,519343,497353,354222,519651,662771,533663,221397,578755,634221,492201,641358,642724,260095,232856,241193,545959,482782,224184,350771,331560,329141,668351,668438,668349,668117,584978,230669,667876,249009,221737,221639,221778,253115,360333,482883,230436,640496,237039,229345,482882,241171,241173,267457,230678,349661,233229,249008,502613,249061,233237,241182,522561,241183,264620,584833,249077,639060,522007,257892,558005,251732,249637,258965,241815,241186,582754,328304,647770,647768,262491,262521,262522,336183,221545,337075,264639,267247,337081,267259,337568,258975,269255,259111,587744,587743,587742,328305,348875,352131,348877,502209,582334,582332,627068,634509,221789,221774,631134,521734,584717,493186,225271,631132,608452,240719,518457,329639,584837,238404,249636,517478,485242,588063,261604,257414,254483,264318,606415,606416,254431,251759,627057,257410,259114,221266,258502,227681,257153,257635,564476,578380,261747,563723,557436,640542,605346,252061,578378,251840,251968,242814,259851,261602,560676,241649,242819,242286,249068,251755,241772,257900,251757,221810,251815,252279,233368,252057,352291,560946,267991,570517,233369,237886,258641,329223,237041,258622,259838,261613,241175,241170,574215,497356,260005,253137,268975,599260,251824,631032,536094,257736,667835,230108,228011,229342,506940,580290,532835,532836,580288,230680,296862,608451,561293,560910,575478,551023,527243,534128,534131,257625,221283,326480,253614,253616,223078,252842,252571,560677,328639,532306,527232,258442,575504,607704,527138,527193,516783,221313,252444,584378,268030,242807,249093,503832,265712,268024,502208,502576,261636,488142,261749,258404,258176,258174,258172,258169,221238,221280,221715,257898,221793,257896,221851,221852,257631,257629,257412,257307,221850,227685,227687,251842,251820,251738,251836,251834,251846,251748,251736,251817,350175,337566,325291,351041,251656,350177,349668,349667,349666,230105,327008,230434,337570,337122,327006,231362,231535,231541,231842,231849,334967,253649,221234,334965,232201,232206,232330,334379,231533,240722,241164,239944,329218,329221,241645,227185,532578,588062,580679,253613,233235,241177,519395,235105,241181,503902,260618,259849,257944,257942,254480,259844,251829,237045,221495,691891,693696,485243,693694,662768,221797,221731,242595,233370,233366,221790,231484,242727,251822,230610,251818,233247,252442,672214,252156,676746,232198,252059,225750,221739,221274,676290,230038,225739,664543,223987,232904,225760,232983,229288,519242,592301,232181,253997,227327,229285,221525,253990,223293,258450,230037,251753,251749,261635,262731,235109,248009,237429,249232,249231,258448,650761,232988,261759,350176,225269,258610,258608,252456,252570,259119,221796,262134,249639,519120,229232,249635,705933,249630,258444,227325,673190,231857,672253,246372,242827,700829,221554,568853,226525,259282,688487,261751,249631,251657,221419,253763,221301,258165,257902,232906,252443,259277,261742,261718,261221,261752,692785,260617,253759,262524,221179,241651,251742,251746,599492,606414,232925,493077,258440,493074,692297,232770,230687,531474,692610,237038,502207,705669,531251,223246,268554,576858,221487,221166,221165,221925,669416,352793,352792,352791,531239,503810,352293,329432,352289,348842,352217,348414,336185,328357,328346,542154,329227,329641,676374,489316,356927,327010,221265,337124,329225,560675,351042,351040,328171,324576,680168,681645,497191,692888,328124,326321,489519,267282,256481,339490,257363,257356,490635,225216,257302,229604,681575,267261,592281,680322,509341,267253,254489,626651,253621,253138,258637,257151,252334,252330,242830,252452,252447,252331,257361,252319,257939,257350,251970,251966,354223,251969,251847,251843,249084,252454,249086,251848,250397,250395,252826,250398,250458,250396,250394,249065,250459,251825,251832,251831,522237}'::integer[]))
  • Filter: ((job_status_id = ANY ('{1,5}'::integer[])) AND (cid = 12924))
  • Rows Removed by Filter: 8
9. 1.016 1.016 ↑ 1.1 719 1

Index Only Scan using idx_properties_cid_id_property_name on properties p (cost=0.41..198.04 rows=803 width=23) (actual time=0.148..1.016 rows=719 loops=1)

  • Index Cond: (cid = 12924)
  • Heap Fetches: 232
10. 2.868 2.868 ↑ 1.0 1 239

Index Scan using idx_ap_headers on ap_headers ah1 (cost=0.42..2.23 rows=1 width=13) (actual time=0.012..0.012 rows=1 loops=239)

  • Index Cond: (id = j.budget_summary_ap_header_id)
  • Filter: (cid = 12924)
11. 106.594 178.055 ↓ 94.0 94 239

GroupAggregate (cost=4,460.66..4,460.69 rows=1 width=40) (actual time=0.259..0.745 rows=94 loops=239)

  • Group Key: jp.job_id, jp.cid
12. 23.364 71.461 ↓ 1,080.0 1,080 239

Sort (cost=4,460.66..4,460.67 rows=1 width=13) (actual time=0.206..0.299 rows=1,080 loops=239)

  • Sort Key: jp.job_id
  • Sort Method: quicksort Memory: 111kB
13. 1.745 48.097 ↓ 1,327.0 1,327 1

Nested Loop (cost=1.12..4,460.65 rows=1 width=13) (actual time=2.603..48.097 rows=1,327 loops=1)

14. 1.153 41.044 ↓ 663.5 1,327 1

Nested Loop (cost=0.84..4,460.05 rows=2 width=13) (actual time=2.559..41.044 rows=1,327 loops=1)

15. 27.978 27.978 ↓ 19.6 627 1

Index Scan using pk_ap_headers on ap_headers ah2 (cost=0.42..4,381.57 rows=32 width=8) (actual time=2.479..27.978 rows=627 loops=1)

  • Index Cond: (cid = 12924)
  • Filter: (is_posted AND (NOT is_reversed) AND (ap_header_sub_type_id = ANY ('{18,17}'::integer[])) AND (ap_header_type_id = 5))
  • Rows Removed by Filter: 6551
16. 11.913 11.913 ↓ 2.0 2 627

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=0.42..2.44 rows=1 width=17) (actual time=0.014..0.019 rows=2 loops=627)

  • Index Cond: ((cid = 12924) AND (ap_header_id = ah2.id))
  • Filter: (deleted_on IS NULL)
17. 5.308 5.308 ↑ 1.0 1 1,327

Index Scan using pk_job_phases on job_phases jp (cost=0.28..0.30 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=1,327)

  • Index Cond: ((cid = 12924) AND (id = ad.job_phase_id))
18. 0.956 0.956 ↑ 1.0 1 239

Index Scan using pk_job_statuses on job_statuses js (cost=0.13..0.16 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=239)

  • Index Cond: (id = j.job_status_id)
19. 37.762 655.338 ↓ 27.0 81 239

GroupAggregate (cost=225,020.39..225,020.45 rows=3 width=40) (actual time=2.568..2.742 rows=81 loops=239)

  • Group Key: jp_1.job_id, gd.cid
20. 6.511 617.576 ↓ 97.7 293 239

Sort (cost=225,020.39..225,020.39 rows=3 width=13) (actual time=2.559..2.584 rows=293 loops=239)

  • Sort Key: jp_1.job_id
  • Sort Method: quicksort Memory: 42kB
21. 0.408 611.065 ↓ 122.3 367 1

Nested Loop (cost=47.82..225,020.36 rows=3 width=13) (actual time=171.494..611.065 rows=367 loops=1)

22. 59.812 603.330 ↓ 4.3 431 1

Hash Join (cost=47.39..224,929.56 rows=101 width=21) (actual time=171.416..603.330 rows=431 loops=1)

  • Hash Cond: (gd.job_phase_id = jp_1.id)
23. 542.500 542.500 ↑ 1.1 401,601 1

Index Scan using idx_gl_details_cid_gl_header_id on gl_details gd (cost=0.43..223,773.32 rows=422,583 width=17) (actual time=0.090..542.500 rows=401,601 loops=1)

  • Index Cond: (cid = 12924)
  • Filter: (reclass_gl_detail_id IS NULL)
  • Rows Removed by Filter: 130
24. 0.340 1.018 ↑ 1.0 767 1

Hash (cost=37.21..37.21 rows=780 width=12) (actual time=1.018..1.018 rows=767 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
25. 0.678 0.678 ↑ 1.0 767 1

Index Scan using pk_job_phases on job_phases jp_1 (cost=0.28..37.21 rows=780 width=12) (actual time=0.017..0.678 rows=767 loops=1)

  • Index Cond: (cid = 12924)
26. 7.327 7.327 ↑ 1.0 1 431

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.43..0.90 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=431)

  • Index Cond: (id = gd.gl_header_id)
  • Filter: ((reclass_gl_header_id IS NULL) AND (is_template IS FALSE) AND (cid = 12924) AND (gl_header_type_id = 1) AND (gl_header_status_type_id = 1))
  • Rows Removed by Filter: 0