explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MfA5

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 221.044 ↓ 23.0 23 1

Group (cost=783.03..783.06 rows=1 width=644) (actual time=221.018..221.044 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.032 221.015 ↓ 23.0 23 1

Sort (cost=783.03..783.03 rows=1 width=580) (actual time=221.013..221.015 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.026 220.983 ↓ 23.0 23 1

Merge Left Join (cost=772.60..783.02 rows=1 width=580) (actual time=133.150..220.983 rows=23 loops=1)

  • Merge Cond: (j.id = jp_1.job_id)
  • Join Filter: (j.cid = gd.cid)
4. 0.053 187.938 ↓ 23.0 23 1

Nested Loop Left Join (cost=443.67..453.77 rows=1 width=548) (actual time=102.392..187.938 rows=23 loops=1)

  • Join Filter: ((j.cid = jp.cid) AND (j.id = jp.job_id))
  • Rows Removed by Join Filter: 253
5. 0.014 0.389 ↓ 23.0 23 1

Nested Loop (cost=0.84..10.89 rows=1 width=516) (actual time=0.037..0.389 rows=23 loops=1)

6. 0.020 0.260 ↓ 23.0 23 1

Nested Loop (cost=0.42..8.24 rows=1 width=506) (actual time=0.028..0.260 rows=23 loops=1)

7. 0.024 0.171 ↓ 23.0 23 1

Nested Loop (cost=0.14..5.69 rows=1 width=490) (actual time=0.020..0.171 rows=23 loops=1)

  • Join Filter: (j.job_type_id = jt.id)
  • Rows Removed by Join Filter: 23
8. 0.048 0.124 ↓ 23.0 23 1

Nested Loop (cost=0.14..4.62 rows=1 width=276) (actual time=0.016..0.124 rows=23 loops=1)

  • Join Filter: (j.job_status_id = js.id)
  • Rows Removed by Join Filter: 75
9. 0.030 0.030 ↓ 23.0 23 1

Index Scan using pk_jobs on jobs j (cost=0.14..2.44 rows=1 width=158) (actual time=0.006..0.030 rows=23 loops=1)

  • Index Cond: (cid = 14,108)
  • Filter: ((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[])))
10. 0.046 0.046 ↑ 2.0 4 23

Seq Scan on job_statuses js (cost=0.00..2.08 rows=8 width=126) (actual time=0.001..0.002 rows=4 loops=23)

11. 0.023 0.023 ↑ 1.5 2 23

Seq Scan on job_types jt (cost=0.00..1.03 rows=3 width=222) (actual time=0.001..0.001 rows=2 loops=23)

12. 0.069 0.069 ↑ 1.0 1 23

Index Scan using idx_properties_id on properties p (cost=0.28..2.50 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=23)

  • Index Cond: (id = j.property_id)
  • Filter: (cid = 14,108)
13. 0.115 0.115 ↑ 1.0 1 23

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

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

GroupAggregate (cost=442.83..442.85 rows=1 width=40) (actual time=5.219..8.152 rows=12 loops=23)

  • Group Key: jp.cid, jp.job_id
15. 28.133 123.763 ↓ 19,889.0 19,889 23

Sort (cost=442.83..442.83 rows=1 width=14) (actual time=4.415..5.381 rows=19,889 loops=23)

  • Sort Key: jp.job_id
  • Sort Method: quicksort Memory: 2,190kB
16. 6.290 95.630 ↓ 30,156.0 30,156 1

Nested Loop (cost=0.85..442.82 rows=1 width=14) (actual time=0.029..95.630 rows=30,156 loops=1)

17. 4.143 26.902 ↓ 743.3 31,219 1

Merge Join (cost=0.43..350.88 rows=42 width=22) (actual time=0.018..26.902 rows=31,219 loops=1)

  • Merge Cond: (jp.id = ad.job_phase_id)
18. 0.055 0.055 ↑ 1.0 124 1

Index Scan using pk_job_phases on job_phases jp (cost=0.14..7.70 rows=124 width=12) (actual time=0.006..0.055 rows=124 loops=1)

  • Index Cond: (cid = 14,108)
19. 22.704 22.704 ↓ 10.1 31,219 1

Index Scan using idx_ap_details_job_phase_id_partial on ap_details ad (cost=0.29..25,108.77 rows=3,083 width=18) (actual time=0.009..22.704 rows=31,219 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
20. 62.438 62.438 ↑ 1.0 1 31,219

Index Scan using idx_ap_headers_id on ap_headers ah2 (cost=0.42..2.19 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=31,219)

  • Index Cond: (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 (cid = 14,108) AND (ap_header_type_id = 5))
  • Rows Removed by Filter: 0
21. 0.010 33.019 ↓ 2.8 22 1

Materialize (cost=328.93..329.21 rows=8 width=40) (actual time=30.753..33.019 rows=22 loops=1)

22. 1.727 33.009 ↓ 2.8 22 1

GroupAggregate (cost=328.93..329.11 rows=8 width=40) (actual time=30.751..33.009 rows=22 loops=1)

  • Group Key: gd.cid, jp_1.job_id
23. 2.790 31.282 ↓ 1,537.5 12,300 1

Sort (cost=328.93..328.95 rows=8 width=26) (actual time=30.661..31.282 rows=12,300 loops=1)

  • Sort Key: jp_1.job_id
  • Sort Method: quicksort Memory: 967kB
24. 10.243 28.492 ↓ 1,537.5 12,300 1

Nested Loop (cost=6.53..328.81 rows=8 width=26) (actual time=0.070..28.492 rows=12,300 loops=1)

25. 2.988 4.697 ↓ 387.2 13,552 1

Hash Join (cost=6.10..235.71 rows=35 width=30) (actual time=0.058..4.697 rows=13,552 loops=1)

  • Hash Cond: (gd.job_phase_id = jp_1.id)
26. 1.670 1.670 ↓ 237.8 13,552 1

Seq Scan on temp_gl_details gd (cost=0.00..229.46 rows=57 width=30) (actual time=0.012..1.670 rows=13,552 loops=1)

  • Filter: (cid = 14,108)
27. 0.016 0.039 ↑ 1.0 124 1

Hash (cost=4.55..4.55 rows=124 width=12) (actual time=0.039..0.039 rows=124 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
28. 0.023 0.023 ↑ 1.0 124 1

Seq Scan on job_phases jp_1 (cost=0.00..4.55 rows=124 width=12) (actual time=0.005..0.023 rows=124 loops=1)

  • Filter: (cid = 14,108)
29. 13.552 13.552 ↑ 1.0 1 13,552

Index Scan using idx_gl_headers_id on gl_headers gh (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=13,552)

  • 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
Planning time : 2.665 ms
Execution time : 222.771 ms