explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9BgZ : Optimization for: plan #UWu1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.036 375.582 ↓ 23.0 23 1

Group (cost=950.28..950.32 rows=1 width=221) (actual time=375.539..375.582 rows=23 loops=1)

  • Group Key: js.order_num, j.cid, j.id, p.property_name, jt.name, js.name, ah1.id, ah1.cid, (sum(ad.transaction_amount)), (sum(gd.amount))
2. 0.087 375.546 ↓ 23.0 23 1

Sort (cost=950.28..950.28 rows=1 width=157) (actual time=375.532..375.546 rows=23 loops=1)

  • Sort Key: js.order_num, j.id, p.property_name, jt.name, js.name, ah1.id, (sum(ad.transaction_amount)), (sum(gd.amount))
  • Sort Method: quicksort Memory: 30kB
3. 0.055 375.459 ↓ 23.0 23 1

Nested Loop Left Join (cost=946.64..950.27 rows=1 width=157) (actual time=264.511..375.459 rows=23 loops=1)

  • Join Filter: ((j.cid = jp_1.cid) AND (j.id = jp_1.job_id))
  • Rows Removed by Join Filter: 253
4. 0.027 74.426 ↓ 23.0 23 1

Nested Loop (cost=575.97..579.49 rows=1 width=125) (actual time=70.349..74.426 rows=23 loops=1)

5. 0.040 73.640 ↓ 23.0 23 1

Nested Loop (cost=575.55..577.03 rows=1 width=115) (actual time=70.278..73.640 rows=23 loops=1)

6. 0.052 73.554 ↓ 23.0 23 1

Nested Loop (cost=575.42..576.57 rows=1 width=106) (actual time=70.267..73.554 rows=23 loops=1)

7. 0.061 73.364 ↓ 23.0 23 1

Merge Left Join (cost=575.28..575.94 rows=1 width=97) (actual time=70.239..73.364 rows=23 loops=1)

  • Merge Cond: (j.id = jp.job_id)
  • Join Filter: (j.cid = gd.cid)
8. 0.025 0.511 ↓ 23.0 23 1

Sort (cost=12.56..12.56 rows=1 width=65) (actual time=0.499..0.511 rows=23 loops=1)

  • Sort Key: j.id
  • Sort Method: quicksort Memory: 28kB
9. 0.019 0.486 ↓ 23.0 23 1

Merge Join (cost=9.23..12.55 rows=1 width=65) (actual time=0.111..0.486 rows=23 loops=1)

  • Merge Cond: (j.property_id = p.id)
10. 0.032 0.053 ↓ 1.0 23 1

Sort (cost=5.22..5.28 rows=22 width=49) (actual time=0.049..0.053 rows=23 loops=1)

  • Sort Key: j.property_id
  • Sort Method: quicksort Memory: 28kB
11. 0.021 0.021 ↓ 1.0 23 1

Seq Scan on jobs j (cost=0.00..4.73 rows=22 width=49) (actual time=0.007..0.021 rows=23 loops=1)

  • Filter: ((cid = 14,108) AND (job_status_id = ANY ('{1,2,3,5,7,4}'::integer[])) AND (property_id = ANY ('{496457,496458,496459,496460,496461,496463,496464,496465,496466,496467,496468,496469,496471,496474,496475,496476,496477,496478,497471,502580,502581,502587,529775,536654,551296,551297,551298,564187,566641,566642,566643,566644,566645,566646,566647,570399,570400,581231,591952,602058,613824,636810,636811,636812,639335,649212,699373,754152,759053,826843,826844,826845,826846,826847,833670,890039,917241,927883,927921,1027376,1049151,1058684,1065363}'::integer[])))
12. 0.414 0.414 ↑ 1.0 73 1

Index Only Scan using idx_properties_cid_id_property_name on properties p (cost=0.28..7.09 rows=75 width=28) (actual time=0.049..0.414 rows=73 loops=1)

  • Index Cond: (cid = 14,108)
  • Heap Fetches: 48
13. 0.038 72.792 ↓ 2.8 22 1

Materialize (cost=562.73..563.33 rows=8 width=40) (actual time=69.734..72.792 rows=22 loops=1)

14. 2.387 72.754 ↓ 2.8 22 1

GroupAggregate (cost=562.73..563.07 rows=8 width=40) (actual time=69.708..72.754 rows=22 loops=1)

  • Group Key: gd.cid, jp.job_id
15. 3.921 70.367 ↓ 1,537.8 12,302 1

Sort (cost=562.73..562.75 rows=8 width=26) (actual time=69.521..70.367 rows=12,302 loops=1)

  • Sort Key: jp.job_id
  • Sort Method: quicksort Memory: 967kB
16. 5.214 66.446 ↓ 1,537.8 12,302 1

Nested Loop (cost=0.58..562.61 rows=8 width=26) (actual time=0.594..66.446 rows=12,302 loops=1)

17. 4.756 20.570 ↓ 387.3 13,554 1

Nested Loop (cost=0.14..475.81 rows=35 width=30) (actual time=0.539..20.570 rows=13,554 loops=1)

18. 2.260 2.260 ↓ 237.8 13,554 1

Seq Scan on temp_gl_details gd (cost=0.00..457.40 rows=57 width=30) (actual time=0.013..2.260 rows=13,554 loops=1)

  • Filter: (cid = 14,108)
19. 13.554 13.554 ↑ 1.0 1 13,554

Index Scan using pk_job_phases on job_phases jp (cost=0.14..0.32 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=13,554)

  • Index Cond: ((cid = 14,108) AND (id = gd.job_phase_id))
20. 40.662 40.662 ↑ 1.0 1 13,554

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.43..2.48 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=13,554)

  • Index Cond: (id = gd.gl_header_id)
  • Filter: ((is_template IS FALSE) AND (reclass_gl_header_id IS NULL) AND (cid = 14,108) AND (gl_header_type_id = 1) AND (gl_header_status_type_id = 1))
  • Rows Removed by Filter: 0
21. 0.138 0.138 ↑ 1.0 1 23

Index Scan using pk_job_statuses on job_statuses js (cost=0.13..0.53 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=23)

  • Index Cond: (id = j.job_status_id)
22. 0.046 0.046 ↑ 1.0 1 23

Index Scan using pk_job_types on job_types jt (cost=0.13..0.35 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (id = j.job_type_id)
23. 0.759 0.759 ↑ 1.0 1 23

Index Scan using idx_ap_headers_id on ap_headers ah1 (cost=0.42..2.47 rows=1 width=14) (actual time=0.033..0.033 rows=1 loops=23)

  • Index Cond: (id = j.budget_summary_ap_header_id)
  • Filter: (cid = 14,108)
  • Rows Removed by Filter: 0
24. 84.962 300.978 ↓ 12.0 12 23

GroupAggregate (cost=370.67..370.71 rows=1 width=40) (actual time=9.409..13.086 rows=12 loops=23)

  • Group Key: jp_1.cid, jp_1.job_id
25. 30.441 216.016 ↓ 19,906.0 19,906 23

Sort (cost=370.67..370.67 rows=1 width=14) (actual time=8.398..9.392 rows=19,906 loops=23)

  • Sort Key: jp_1.job_id
  • Sort Method: quicksort Memory: 2,193kB
26. 16.821 185.575 ↓ 30,221.0 30,221 1

Nested Loop (cost=0.85..370.66 rows=1 width=14) (actual time=0.122..185.575 rows=30,221 loops=1)

27. 4.756 74.902 ↓ 845.5 31,284 1

Merge Join (cost=0.43..291.88 rows=37 width=22) (actual time=0.065..74.902 rows=31,284 loops=1)

  • Merge Cond: (jp_1.id = ad.job_phase_id)
28. 0.150 0.150 ↑ 1.0 124 1

Index Scan using pk_job_phases on job_phases jp_1 (cost=0.14..12.79 rows=124 width=12) (actual time=0.010..0.150 rows=124 loops=1)

  • Index Cond: (cid = 14,108)
29. 69.996 69.996 ↓ 10.9 31,284 1

Index Scan using idx_ap_details_job_phase_id_partial on ap_details ad (cost=0.29..21,493.94 rows=2,868 width=18) (actual time=0.050..69.996 rows=31,284 loops=1)

  • Filter: ((deleted_on IS NULL) AND (deleted_by IS NULL) AND (reversal_ap_detail_id IS NULL) AND (cid = 14,108))
  • Rows Removed by Filter: 298
30. 93.852 93.852 ↑ 1.0 1 31,284

Index Scan using pk_ap_headers on ap_headers ah2 (cost=0.42..2.13 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=31,284)

  • Index Cond: ((cid = 14,108) AND (id = ad.ap_header_id))
  • Filter: ((is_posted IS TRUE) AND (deleted_on IS NULL) AND (ap_header_sub_type_id = ANY ('{18,17}'::integer[])) AND (ap_header_type_id = 5))
  • Rows Removed by Filter: 0
Planning time : 9.234 ms
Execution time : 377.477 ms