explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ghouM

Settings
# exclusive inclusive rows x rows loops node
1. 145.531 13,407.537 ↑ 1.6 131,321 1

Merge Join (cost=421,805.26..424,944.30 rows=208,175 width=284) (actual time=13,113.653..13,407.537 rows=131,321 loops=1)

  • Merge Cond: (acr.acr_sess_id = sess_anim.sess_id)
2.          

CTE acr

3. 1,795.475 7,247.877 ↑ 1.0 160,057 1

GroupAggregate (cost=60.32..363,519.29 rows=160,166 width=72) (actual time=0.166..7,247.877 rows=160,057 loops=1)

  • Group Key: tbl_activity_result.acr_id
4. 1,971.765 5,452.402 ↑ 1.0 2,694,941 1

Merge Join (cost=60.32..334,546.96 rows=2,697,025 width=56) (actual time=0.133..5,452.402 rows=2,694,941 loops=1)

  • Merge Cond: (tbl_activity_result.acr_id = tbl_activity_result_action.ara_acr_id)
5. 370.620 370.620 ↑ 1.0 160,166 1

Index Scan using tbl_activity_result_pkey on tbl_activity_result (cost=0.42..11,490.81 rows=160,166 width=48) (actual time=0.077..370.620 rows=160,166 loops=1)

6. 3,110.017 3,110.017 ↑ 1.0 2,694,941 1

Index Scan using tbl_activity_result_action_acr_id_btree on tbl_activity_result_action (cost=0.43..289,002.39 rows=2,697,025 width=24) (actual time=0.046..3,110.017 rows=2,694,941 loops=1)

7.          

CTE sess

8. 412.618 505.313 ↑ 1.0 12,682 1

GroupAggregate (cost=2,309.63..2,753.50 rows=12,682 width=120) (actual time=76.347..505.313 rows=12,682 loops=1)

  • Group Key: tbl_session.sess_id
9. 33.566 92.695 ↓ 1.1 13,491 1

Sort (cost=2,309.63..2,341.34 rows=12,682 width=87) (actual time=76.237..92.695 rows=13,491 loops=1)

  • Sort Key: tbl_session.sess_id
  • Sort Method: quicksort Memory: 2282kB
10. 14.011 59.129 ↓ 1.1 13,491 1

Hash Right Join (cost=473.48..1,445.32 rows=12,682 width=87) (actual time=16.511..59.129 rows=13,491 loops=1)

  • Hash Cond: (tbl_session_member.sesm_sess_id = tbl_session.sess_id)
11. 10.162 30.039 ↑ 1.0 10,456 1

Hash Left Join (cost=40.14..868.14 rows=10,461 width=47) (actual time=1.383..30.039 rows=10,456 loops=1)

  • Hash Cond: (tbl_session_member.sesm_climem_id = tbl_supervisor.sup_id)
12. 18.594 18.594 ↑ 1.0 10,456 1

Seq Scan on tbl_session_member (cost=0.00..749.48 rows=10,461 width=32) (actual time=0.064..18.594 rows=10,456 loops=1)

  • Filter: (sesm_mem_type = 'contributor'::text)
  • Rows Removed by Filter: 20542
13. 0.680 1.283 ↑ 1.0 1,206 1

Hash (cost=25.06..25.06 rows=1,206 width=31) (actual time=1.283..1.283 rows=1,206 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
14. 0.603 0.603 ↑ 1.0 1,206 1

Seq Scan on tbl_supervisor (cost=0.00..25.06 rows=1,206 width=31) (actual time=0.026..0.603 rows=1,206 loops=1)

15. 7.580 15.079 ↑ 1.0 12,682 1

Hash (cost=274.82..274.82 rows=12,682 width=56) (actual time=15.079..15.079 rows=12,682 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1222kB
16. 7.499 7.499 ↑ 1.0 12,682 1

Seq Scan on tbl_session (cost=0.00..274.82 rows=12,682 width=56) (actual time=0.057..7.499 rows=12,682 loops=1)

17.          

CTE sess_anim

18. 684.719 976.405 ↑ 1.0 12,682 1

CTE Scan on sess (cost=0.00..25,681.05 rows=12,682 width=144) (actual time=76.453..976.405 rows=12,682 loops=1)

19.          

SubPlan (forCTE Scan)

20. 291.686 291.686 ↑ 100.0 1 12,682

Function Scan on json_array_elements (cost=0.00..2.00 rows=100 width=32) (actual time=0.022..0.023 rows=1 loops=12,682)

21.          

CTE acr_times

22. 2,423.139 10,767.151 ↓ 9.2 147,536 1

GroupAggregate (cost=23,618.54..27,943.03 rows=16,017 width=104) (actual time=8,073.186..10,767.151 rows=147,536 loops=1)

  • Group Key: acr_1.start_date, acr_1.acr_act_id, acr_1.acr_sess_id
23. 681.702 8,344.012 ↑ 1.0 160,057 1

Sort (cost=23,618.54..24,018.95 rows=160,166 width=72) (actual time=8,073.086..8,344.012 rows=160,057 loops=1)

  • Sort Key: acr_1.start_date, acr_1.acr_act_id, acr_1.acr_sess_id
  • Sort Method: external merge Disk: 12688kB
24. 7,662.310 7,662.310 ↑ 1.0 160,057 1

CTE Scan on acr acr_1 (cost=0.00..3,203.32 rows=160,166 width=72) (actual time=0.168..7,662.310 rows=160,057 loops=1)

25. 618.654 12,155.692 ↓ 42.9 140,879 1

Sort (cost=606.90..615.10 rows=3,283 width=120) (actual time=12,039.601..12,155.692 rows=140,879 loops=1)

  • Sort Key: acr.acr_sess_id
  • Sort Method: external merge Disk: 12200kB
26. 343.666 11,537.038 ↓ 44.9 147,536 1

Hash Join (cost=1.92..415.16 rows=3,283 width=120) (actual time=8,073.322..11,537.038 rows=147,536 loops=1)

  • Hash Cond: (acr.acr_act_id = tbl_activity.act_id)
27. 11,193.281 11,193.281 ↓ 9.2 147,536 1

CTE Scan on acr_times acr (cost=0.00..320.34 rows=16,017 width=104) (actual time=8,073.195..11,193.281 rows=147,536 loops=1)

28. 0.023 0.091 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=48) (actual time=0.091..0.091 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 0.068 0.068 ↑ 1.0 41 1

Seq Scan on tbl_activity (cost=0.00..1.41 rows=41 width=48) (actual time=0.051..0.068 rows=41 loops=1)

30. 59.158 1,106.314 ↓ 10.4 131,359 1

Sort (cost=1,301.50..1,333.21 rows=12,682 width=170) (actual time=1,074.037..1,106.314 rows=131,359 loops=1)

  • Sort Key: sess_anim.sess_id
  • Sort Method: quicksort Memory: 2428kB
31. 40.310 1,047.156 ↑ 1.0 12,682 1

Hash Join (cost=9.18..437.19 rows=12,682 width=170) (actual time=76.830..1,047.156 rows=12,682 loops=1)

  • Hash Cond: (sess_anim.sess_cli_id = tbl_client.cli_id)
32. 1,006.501 1,006.501 ↑ 1.0 12,682 1

CTE Scan on sess_anim (cost=0.00..253.64 rows=12,682 width=144) (actual time=76.461..1,006.501 rows=12,682 loops=1)

33. 0.118 0.345 ↑ 1.0 230 1

Hash (cost=6.30..6.30 rows=230 width=26) (actual time=0.345..0.345 rows=230 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
34. 0.227 0.227 ↑ 1.0 230 1

Seq Scan on tbl_client (cost=0.00..6.30 rows=230 width=26) (actual time=0.052..0.227 rows=230 loops=1)