explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UWu1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.032 5,860.049 ↓ 23.0 23 1

Group (cost=7,743.39..7,743.43 rows=1 width=221) (actual time=5,860.005..5,860.049 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.134 5,860.017 ↓ 23.0 23 1

Sort (cost=7,743.39..7,743.40 rows=1 width=157) (actual time=5,859.999..5,860.017 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.290 5,859.883 ↓ 23.0 23 1

Nested Loop Left Join (cost=2,095.64..7,743.38 rows=1 width=157) (actual time=235.587..5,859.883 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.076 67.825 ↓ 23.0 23 1

Nested Loop (cost=575.91..579.43 rows=1 width=125) (actual time=61.731..67.825 rows=23 loops=1)

5. 0.082 65.518 ↓ 23.0 23 1

Nested Loop (cost=575.49..576.97 rows=1 width=115) (actual time=61.675..65.518 rows=23 loops=1)

6. 0.106 65.321 ↓ 23.0 23 1

Nested Loop (cost=575.36..576.51 rows=1 width=106) (actual time=61.665..65.321 rows=23 loops=1)

7. 0.138 64.962 ↓ 23.0 23 1

Merge Left Join (cost=575.23..575.89 rows=1 width=97) (actual time=61.641..64.962 rows=23 loops=1)

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

Sort (cost=12.50..12.50 rows=1 width=65) (actual time=0.494..0.521 rows=23 loops=1)

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

Merge Join (cost=9.18..12.49 rows=1 width=65) (actual time=0.110..0.480 rows=23 loops=1)

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

Sort (cost=5.16..5.22 rows=22 width=49) (actual time=0.048..0.052 rows=23 loops=1)

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

Seq Scan on jobs j (cost=0.00..4.67 rows=22 width=49) (actual time=0.009..0.026 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,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}'::integer[])))
12. 0.408 0.408 ↑ 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.053..0.408 rows=73 loops=1)

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

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

14. 2.397 64.280 ↓ 2.8 22 1

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

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

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

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

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

17. 3.966 19.775 ↓ 387.3 13,554 1

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

18. 2.255 2.255 ↓ 237.8 13,554 1

Seq Scan on temp_gl_details gd (cost=0.00..457.40 rows=57 width=30) (actual time=0.012..2.255 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. 27.108 27.108 ↑ 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.002..0.002 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.253 0.253 ↑ 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.011..0.011 rows=1 loops=23)

  • Index Cond: (id = j.job_status_id)
22. 0.115 0.115 ↑ 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.005..0.005 rows=1 loops=23)

  • Index Cond: (id = j.job_type_id)
23. 2.231 2.231 ↑ 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.097..0.097 rows=1 loops=23)

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

GroupAggregate (cost=1,519.72..7,163.89 rows=1 width=40) (actual time=19.754..251.816 rows=12 loops=23)

  • Group Key: jp_1.cid, jp_1.job_id
25. 3,486.386 5,710.647 ↓ 19,906.0 19,906 23

Nested Loop (cost=1,519.72..7,163.85 rows=1 width=14) (actual time=4.363..248.289 rows=19,906 loops=23)

  • Join Filter: (ad.job_phase_id = jp_1.id)
  • Rows Removed by Join Filter: 1,825,142
26. 4.945 4.945 ↑ 2.0 62 23

Index Scan using idx_job_phases_job_id on job_phases jp_1 (cost=0.14..12.79 rows=124 width=12) (actual time=0.007..0.215 rows=62 loops=23)

  • Filter: (cid = 14,108)
27. 2,062.409 2,219.316 ↓ 7,476.5 29,906 1,419

Materialize (cost=1,519.58..7,133.70 rows=4 width=14) (actual time=0.011..1.564 rows=29,906 loops=1,419)

28. 11.039 156.907 ↓ 7,555.2 30,221 1

Nested Loop (cost=1,519.58..7,133.68 rows=4 width=14) (actual time=15.595..156.907 rows=30,221 loops=1)

29. 25.724 40.820 ↓ 10.0 11,672 1

Bitmap Heap Scan on ap_headers ah2 (cost=1,519.15..3,194.02 rows=1,164 width=8) (actual time=15.531..40.820 rows=11,672 loops=1)

  • Recheck Cond: ((ap_header_sub_type_id = ANY ('{18,17}'::integer[])) AND (cid = 14,108))
  • Filter: ((is_posted IS TRUE) AND (deleted_on IS NULL) AND (ap_header_type_id = 5))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=3,162
30. 0.244 15.096 ↓ 0.0 0 1

BitmapAnd (cost=1,519.15..1,519.15 rows=1,650 width=0) (actual time=15.094..15.096 rows=0 loops=1)

31. 1.353 1.353 ↑ 1.1 11,675 1

Bitmap Index Scan on idx_ap_headers_ap_header_sub_type_id (cost=0.00..140.41 rows=12,610 width=0) (actual time=1.353..1.353 rows=11,675 loops=1)

  • Index Cond: (ap_header_sub_type_id = ANY ('{18,17}'::integer[]))
32. 13.499 13.499 ↑ 1.0 125,050 1

Bitmap Index Scan on pk_ap_headers (cost=0.00..1,377.90 rows=125,797 width=0) (actual time=13.499..13.499 rows=125,050 loops=1)

  • Index Cond: (cid = 14,108)
33. 105.048 105.048 ↓ 3.0 3 11,672

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=0.43..3.35 rows=1 width=18) (actual time=0.006..0.009 rows=3 loops=11,672)

  • Index Cond: ((cid = 14,108) AND (ap_header_id = ah2.id))
  • Filter: ((job_phase_id IS NOT NULL) AND (deleted_on IS NULL) AND (deleted_by IS NULL) AND (reversal_ap_detail_id IS NULL))
  • Rows Removed by Filter: 0
Planning time : 9.832 ms
Execution time : 5,862.348 ms