explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oElD

Settings
# exclusive inclusive rows x rows loops node
1. 2,114.018 13,364.144 ↑ 15.9 131,321 1

GroupAggregate (cost=1,261,643.59..1,391,768.84 rows=2,082,004 width=346) (actual time=10,748.255..13,364.144 rows=131,321 loops=1)

  • Group Key: tbl_activity.act_label, acr.start_date, sess_anim.sess_cli_id, sess_anim.cli_name, sess_anim.sess_id, sess_anim.sess_startdate, sess_anim.sess_enddate, sess_anim.sess_type, sess_anim.session_time, sess_anim.sess_animateurs
2.          

CTE acr

3. 1,852.976 7,303.478 ↑ 1.0 160,057 1

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

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

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

  • Merge Cond: (tbl_activity_result.acr_id = tbl_activity_result_action.ara_acr_id)
5. 383.162 383.162 ↑ 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.085..383.162 rows=160,166 loops=1)

6. 3,128.923 3,128.923 ↑ 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.047..3,128.923 rows=2,694,941 loops=1)

7.          

CTE sess

8. 403.691 500.326 ↑ 1.0 12,682 1

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

  • Group Key: tbl_session.sess_id
9. 35.433 96.635 ↓ 1.1 13,491 1

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

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

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

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

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

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

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

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

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

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

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

15. 7.901 16.246 ↑ 1.0 12,682 1

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

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

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

17.          

CTE sess_anim

18. 169.867 984.926 ↑ 1.0 12,682 1

Hash Join (cost=9.18..25,864.60 rows=12,682 width=154) (actual time=78.959..984.926 rows=12,682 loops=1)

  • Hash Cond: (sess.sess_cli_id = tbl_client.cli_id)
19. 535.769 535.769 ↑ 1.0 12,682 1

CTE Scan on sess (cost=0.00..253.64 rows=12,682 width=144) (actual time=78.572..535.769 rows=12,682 loops=1)

20. 0.114 0.286 ↑ 1.0 230 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
21. 0.172 0.172 ↑ 1.0 230 1

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

22.          

SubPlan (forHash Join)

23. 279.004 279.004 ↑ 100.0 1 12,682

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

24. 1,308.567 11,250.126 ↑ 14.9 140,202 1

Sort (cost=869,506.20..874,711.21 rows=2,082,004 width=314) (actual time=10,748.160..11,250.126 rows=140,202 loops=1)

  • Sort Key: tbl_activity.act_label, acr.start_date, sess_anim.sess_cli_id, sess_anim.cli_name, sess_anim.sess_id, sess_anim.sess_startdate, sess_anim.sess_enddate, sess_anim.sess_type, sess_anim.session_time, sess_anim.sess_animateurs
  • Sort Method: external merge Disk: 22320kB
25. 105.979 9,941.559 ↑ 14.9 140,202 1

Merge Join (cost=7,715.18..39,008.65 rows=2,082,004 width=314) (actual time=9,754.324..9,941.559 rows=140,202 loops=1)

  • Merge Cond: (sess_anim.sess_id = acr.acr_sess_id)
26. 31.620 1,055.850 ↑ 1.0 12,682 1

Sort (cost=1,117.95..1,149.65 rows=12,682 width=242) (actual time=1,051.271..1,055.850 rows=12,682 loops=1)

  • Sort Key: sess_anim.sess_id
  • Sort Method: quicksort Memory: 2185kB
27. 1,024.230 1,024.230 ↑ 1.0 12,682 1

CTE Scan on sess_anim (cost=0.00..253.64 rows=12,682 width=242) (actual time=78.965..1,024.230 rows=12,682 loops=1)

28. 771.662 8,779.730 ↓ 4.7 153,124 1

Sort (cost=6,597.23..6,679.32 rows=32,834 width=88) (actual time=8,703.037..8,779.730 rows=153,124 loops=1)

  • Sort Key: acr.acr_sess_id
  • Sort Method: external sort Disk: 12928kB
29. 321.981 8,008.068 ↓ 4.9 160,057 1

Hash Join (cost=1.92..4,134.20 rows=32,834 width=88) (actual time=0.278..8,008.068 rows=160,057 loops=1)

  • Hash Cond: (acr.acr_act_id = tbl_activity.act_id)
30. 7,686.020 7,686.020 ↑ 1.0 160,057 1

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

31. 0.026 0.067 ↑ 1.0 41 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.041 0.041 ↑ 1.0 41 1

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