explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SxMR : Optimization for: Optimization for: plan #Xm5O; plan #aknL

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,643.760 115,661.760 ↓ 2.0 1,637,202 1

Group (cost=51,368,352.47..102,001,970.71 rows=814,576 width=1,909) (actual time=109,483.526..115,661.760 rows=1,637,202 loops=1)

  • Group Key: concession.id_concession, prog.id, period.id, cp.id, collaborator.id_old_archi, collaborator.id_concession, collaborator.contract_name, (replace(COALESCE(((abstractpeople.last_name)::text || (SubPlan 1)), (abstractpeople.last_name)::text), '()'::text, ''::text)), abstractpeople.first_name, ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), collaborator.enabled_at, activity_response.id, reference_activity_response.id, reference_activity_response_value.id, activity.id
2. 27,152.424 114,018.000 ↓ 2.0 1,637,202 1

Sort (cost=51,368,352.47..51,370,388.91 rows=814,576 width=1,909) (actual time=109,483.516..114,018.000 rows=1,637,202 loops=1)

  • Sort Key: concession.id_concession, prog.id, period.id, cp.id, collaborator.id_old_archi, collaborator.id_concession, collaborator.contract_name, (replace(COALESCE(((abstractpeople.last_name)::text || (SubPlan 1)), (abstractpeople.last_name)::text), '()'::text, ''::text)), abstractpeople.first_name, ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), collaborator.enabled_at, activity_response.id, reference_activity_response.id, reference_activity_response_value.id, activity.id
  • Sort Method: external merge Disk: 749296kB
3. 21,446.834 86,865.576 ↓ 2.0 1,637,202 1

Hash Left Join (cost=3,771.34..50,614,600.66 rows=814,576 width=1,909) (actual time=73.713..86,865.576 rows=1,637,202 loops=1)

  • Hash Cond: ((activity.reference_activity_response_id = reference_activity_response.id) AND (reference_activity_response_value.reference_activity_response_id = reference_activity_response.id))
4. 1,363.598 3,205.049 ↓ 2.0 1,637,202 1

Hash Left Join (cost=3,770.27..16,394.05 rows=814,576 width=1,565) (actual time=73.457..3,205.049 rows=1,637,202 loops=1)

  • Hash Cond: (activity_response.reference_activity_response_value_id = reference_activity_response_value.id)
5. 1,073.784 1,841.430 ↓ 2.0 1,637,202 1

Hash Join (cost=3,769.04..13,348.35 rows=814,576 width=1,509) (actual time=73.426..1,841.430 rows=1,637,202 loops=1)

  • Hash Cond: (prog.id = period.integration_program_id)
6. 271.166 767.609 ↓ 1.9 276,257 1

Hash Left Join (cost=3,767.28..11,370.31 rows=143,729 width=989) (actual time=73.378..767.609 rows=276,257 loops=1)

  • Hash Cond: (activity_response.activity_id = activity.id)
7. 348.725 495.926 ↓ 1.9 276,257 1

Hash Right Join (cost=3,744.45..10,966.87 rows=143,729 width=810) (actual time=72.844..495.926 rows=276,257 loops=1)

  • Hash Cond: (activity_response.collaborator_integration_program_id = cp.id)
8. 74.401 74.401 ↑ 1.0 287,500 1

Seq Scan on activity_response (cost=0.00..4,707.00 rows=287,500 width=24) (actual time=0.019..74.401 rows=287,500 loops=1)

9. 5.981 72.800 ↓ 1.9 6,582 1

Hash (cost=3,701.23..3,701.23 rows=3,458 width=790) (actual time=72.800..72.800 rows=6,582 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1347kB
10. 5.454 66.819 ↓ 1.9 6,582 1

Nested Loop (cost=220.88..3,701.23 rows=3,458 width=790) (actual time=7.147..66.819 rows=6,582 loops=1)

11. 3.184 35.037 ↓ 1.9 6,582 1

Hash Join (cost=220.59..2,033.17 rows=3,458 width=774) (actual time=7.117..35.037 rows=6,582 loops=1)

  • Hash Cond: (prog.concession_id = concession.id_concession)
12. 15.086 31.838 ↓ 1.9 6,582 1

Hash Join (cost=219.48..2,016.57 rows=3,458 width=616) (actual time=7.095..31.838 rows=6,582 loops=1)

  • Hash Cond: (collaborator.id_people = cp.collaborator_id)
13. 9.685 9.685 ↑ 1.0 41,492 1

Seq Scan on collaborator (cost=0.00..1,606.92 rows=41,492 width=44) (actual time=0.012..9.685 rows=41,492 loops=1)

14. 2.719 7.067 ↓ 1.9 6,582 1

Hash (cost=176.25..176.25 rows=3,458 width=572) (actual time=7.066..7.067 rows=6,582 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 887kB
15. 3.166 4.348 ↓ 1.9 6,582 1

Hash Join (cost=1.10..176.25 rows=3,458 width=572) (actual time=0.036..4.348 rows=6,582 loops=1)

  • Hash Cond: (cp.integration_program_id = prog.id)
16. 1.166 1.166 ↑ 1.0 6,917 1

Seq Scan on collaborator_integration_program cp (cost=0.00..144.17 rows=6,917 width=48) (actual time=0.010..1.166 rows=6,917 loops=1)

17. 0.003 0.016 ↑ 1.0 3 1

Hash (cost=1.06..1.06 rows=3 width=524) (actual time=0.015..0.016 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.013 0.013 ↑ 1.0 3 1

Seq Scan on integration_program prog (cost=0.00..1.06 rows=3 width=524) (actual time=0.011..0.013 rows=3 loops=1)

  • Filter: enable
  • Rows Removed by Filter: 3
19. 0.004 0.015 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=162) (actual time=0.014..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.011 0.011 ↑ 1.0 5 1

Seq Scan on concession (cost=0.00..1.05 rows=5 width=162) (actual time=0.009..0.011 rows=5 loops=1)

21. 26.328 26.328 ↑ 1.0 1 6,582

Index Scan using abstractpeople_pkey on abstractpeople (cost=0.29..0.48 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=6,582)

  • Index Cond: (id_people = collaborator.id_people)
22. 0.254 0.517 ↑ 1.0 481 1

Hash (cost=16.81..16.81 rows=481 width=179) (actual time=0.517..0.517 rows=481 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 92kB
23. 0.263 0.263 ↑ 1.0 481 1

Seq Scan on activity (cost=0.00..16.81 rows=481 width=179) (actual time=0.024..0.263 rows=481 loops=1)

  • Filter: (NOT is_disabled)
24. 0.012 0.037 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=528) (actual time=0.037..0.037 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
25. 0.025 0.025 ↑ 1.0 34 1

Seq Scan on period (cost=0.00..1.34 rows=34 width=528) (actual time=0.018..0.025 rows=34 loops=1)

26. 0.009 0.021 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=56) (actual time=0.021..0.021 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.012 0.012 ↑ 1.0 10 1

Seq Scan on reference_activity_response_value (cost=0.00..1.10 rows=10 width=56) (actual time=0.009..0.012 rows=10 loops=1)

28. 0.008 0.017 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=150) (actual time=0.017..0.017 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.009 0.009 ↑ 1.0 3 1

Seq Scan on reference_activity_response (cost=0.00..1.03 rows=3 width=150) (actual time=0.007..0.009 rows=3 loops=1)

30.          

SubPlan (forHash Left Join)

31. 6,548.808 6,548.808 ↑ 1.0 1 1,637,202

Index Scan using abstractpeople_pkey on abstractpeople jf (cost=0.29..8.31 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1,637,202)

  • Index Cond: (id_people = collaborator.id_people)
  • Filter: (birth_name IS NOT NULL)
  • Rows Removed by Filter: 0
32. 1,637.202 9,823.212 ↑ 1.0 1 1,637,202

Nested Loop (cost=0.58..16.62 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1,637,202)

33. 4,911.606 4,911.606 ↑ 1.0 1 1,637,202

Index Scan using idx_d20dd34d43ac3d0e on genealogy (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,637,202)

  • Index Cond: (id_collaborator = collaborator.id_people)
34. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using genealogy_pkey on genealogy niv0 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_genealogy = genealogy.id_tutor)
35. 1,637.202 13,097.616 ↑ 1.0 1 1,637,202

Nested Loop (cost=0.87..17.05 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1,637,202)

36. 1,637.202 8,186.010 ↑ 1.0 1 1,637,202

Nested Loop (cost=0.58..16.62 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1,637,202)

37. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using idx_d20dd34d43ac3d0e on genealogy genealogy_1 (cost=0.29..8.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_collaborator = collaborator.id_people)
38. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using genealogy_pkey on genealogy niv0_1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_genealogy = genealogy_1.id_tutor)
39. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using collaborator_pkey on collaborator parrain (cost=0.29..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_people = niv0_1.id_collaborator)
40. 0.000 11,460.414 ↑ 1.0 1 1,637,202

Nested Loop (cost=0.87..17.05 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,637,202)

41. 1,637.202 8,186.010 ↑ 1.0 1 1,637,202

Nested Loop (cost=0.58..16.62 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1,637,202)

42. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using idx_d20dd34d43ac3d0e on genealogy genealogy_2 (cost=0.29..8.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_collaborator = collaborator.id_people)
43. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using genealogy_pkey on genealogy niv0_2 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_genealogy = genealogy_2.id_tutor)
44. 3,274.404 3,274.404 ↑ 1.0 1 1,637,202

Index Scan using collaborator_pkey on collaborator parrain_1 (cost=0.29..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,637,202)

  • Index Cond: (id_people = niv0_2.id_collaborator)
45. 11,460.414 11,460.414 ↑ 1.0 1 1,637,202

Seq Scan on constellation (cost=0.00..1.54 rows=1 width=24) (actual time=0.005..0.007 rows=1 loops=1,637,202)

  • Filter: (id = collaborator.id_constellation)
  • Rows Removed by Filter: 42
46. 9,823.212 9,823.212 ↑ 1.0 1 1,637,202

Seq Scan on constellation constellation_1 (cost=0.00..1.54 rows=1 width=146) (actual time=0.004..0.006 rows=1 loops=1,637,202)

  • Filter: (id = collaborator.id_constellation)
  • Rows Removed by Filter: 42
Planning time : 7.685 ms