explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LLFJ : Optimization for: sous requete concession

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 34,924.611 117,501.434 ↓ 2.0 1,640,588 1

Hash Left Join (cost=3,759.20..53,315,383.78 rows=816,214 width=1,323) (actual time=120.293..117,501.434 rows=1,640,588 loops=1)

  • Hash Cond: (reference_activity_response_value.reference_activity_response_id = reference_activity_response.id)
2. 1,059.992 2,187.974 ↓ 2.0 1,640,588 1

Hash Join (cost=3,758.14..13,895.55 rows=816,214 width=927) (actual time=119.764..2,187.974 rows=1,640,588 loops=1)

  • Hash Cond: (prog.id = period.integration_program_id)
3. 218.384 1,127.915 ↓ 1.9 276,832 1

Hash Left Join (cost=3,756.37..11,912.96 rows=144,060 width=407) (actual time=119.672..1,127.915 rows=276,832 loops=1)

  • Hash Cond: (activity_response.reference_activity_response_value_id = reference_activity_response_value.id)
4. 296.912 909.490 ↓ 1.9 276,832 1

Hash Left Join (cost=3,755.15..11,373.31 rows=144,060 width=351) (actual time=119.612..909.490 rows=276,832 loops=1)

  • Hash Cond: (activity_response.activity_id = activity.id)
5. 405.077 611.495 ↓ 1.9 276,832 1

Hash Right Join (cost=3,732.32..10,969.00 rows=144,060 width=172) (actual time=118.498..611.495 rows=276,832 loops=1)

  • Hash Cond: (activity_response.collaborator_integration_program_id = cp.id)
6. 87.993 87.993 ↑ 1.0 288,078 1

Seq Scan on activity_response (cost=0.00..4,715.78 rows=288,078 width=24) (actual time=0.031..87.993 rows=288,078 loops=1)

7. 9.754 118.425 ↓ 1.9 6,588 1

Hash (cost=3,689.05..3,689.05 rows=3,462 width=152) (actual time=118.425..118.425 rows=6,588 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1295kB
8. 16.279 108.671 ↓ 1.9 6,588 1

Nested Loop (cost=220.90..3,689.05 rows=3,462 width=152) (actual time=12.721..108.671 rows=6,588 loops=1)

9. 24.564 52.864 ↓ 1.9 6,588 1

Hash Join (cost=220.61..2,019.15 rows=3,462 width=136) (actual time=12.664..52.864 rows=6,588 loops=1)

  • Hash Cond: (collaborator.id_people = cp.collaborator_id)
10. 15.695 15.695 ↑ 1.0 41,521 1

Seq Scan on collaborator (cost=0.00..1,608.21 rows=41,521 width=44) (actual time=0.024..15.695 rows=41,521 loops=1)

11. 4.581 12.605 ↓ 1.9 6,588 1

Hash (cost=177.34..177.34 rows=3,462 width=92) (actual time=12.605..12.605 rows=6,588 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 888kB
12. 5.987 8.024 ↓ 1.9 6,588 1

Hash Join (cost=1.10..177.34 rows=3,462 width=92) (actual time=0.070..8.024 rows=6,588 loops=1)

  • Hash Cond: (cp.integration_program_id = prog.id)
13. 2.011 2.011 ↑ 1.0 6,923 1

Seq Scan on collaborator_integration_program cp (cost=0.00..145.23 rows=6,923 width=48) (actual time=0.025..2.011 rows=6,923 loops=1)

14. 0.007 0.026 ↑ 1.0 3 1

Hash (cost=1.06..1.06 rows=3 width=44) (actual time=0.025..0.026 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.019 0.019 ↑ 1.0 3 1

Seq Scan on integration_program prog (cost=0.00..1.06 rows=3 width=44) (actual time=0.016..0.019 rows=3 loops=1)

  • Filter: enable
  • Rows Removed by Filter: 3
16. 39.528 39.528 ↑ 1.0 1 6,588

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

  • Index Cond: (id_people = collaborator.id_people)
17. 0.529 1.083 ↑ 1.0 481 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 92kB
18. 0.554 0.554 ↑ 1.0 481 1

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

  • Filter: (NOT is_disabled)
19. 0.017 0.041 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.024 0.024 ↑ 1.0 10 1

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

21. 0.023 0.067 ↑ 1.0 34 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.044 0.044 ↑ 1.0 34 1

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

23. 0.021 0.037 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=44) (actual time=0.037..0.037 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.016 0.016 ↑ 1.0 3 1

Seq Scan on reference_activity_response (cost=0.00..1.03 rows=3 width=44) (actual time=0.013..0.016 rows=3 loops=1)

25.          

SubPlan (forHash Left Join)

26. 4,921.764 4,921.764 ↑ 1.0 1 1,640,588

Seq Scan on concession (cost=0.00..1.06 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,640,588)

  • Filter: (id_concession = prog.concession_id)
  • Rows Removed by Filter: 4
27. 4,921.764 4,921.764 ↑ 1.0 1 1,640,588

Seq Scan on concession concession_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1,640,588)

  • Filter: (id_concession = prog.concession_id)
  • Rows Removed by Filter: 4
28. 4,921.764 4,921.764 ↑ 1.0 1 1,640,588

Seq Scan on concession concession_2 (cost=0.00..1.06 rows=1 width=3) (actual time=0.003..0.003 rows=1 loops=1,640,588)

  • Filter: (id_concession = prog.concession_id)
  • Rows Removed by Filter: 4
29. 8,202.940 8,202.940 ↑ 1.0 1 1,640,588

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

  • Index Cond: (id_people = collaborator.id_people)
  • Filter: (birth_name IS NOT NULL)
  • Rows Removed by Filter: 0
30. 1,640.588 9,843.528 ↑ 1.0 1 1,640,588

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

31. 4,921.764 4,921.764 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_collaborator = collaborator.id_people)
32. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_genealogy = genealogy.id_tutor)
33. 1,640.588 13,124.704 ↑ 1.0 1 1,640,588

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

34. 1,640.588 8,202.940 ↑ 1.0 1 1,640,588

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

35. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_collaborator = collaborator.id_people)
36. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_genealogy = genealogy_1.id_tutor)
37. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_people = niv0_1.id_collaborator)
38. 1,640.588 13,124.704 ↑ 1.0 1 1,640,588

Nested Loop (cost=0.87..17.05 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1,640,588)

39. 1,640.588 8,202.940 ↑ 1.0 1 1,640,588

Nested Loop (cost=0.58..16.62 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,640,588)

40. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_collaborator = collaborator.id_people)
41. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_genealogy = genealogy_2.id_tutor)
42. 3,281.176 3,281.176 ↑ 1.0 1 1,640,588

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,640,588)

  • Index Cond: (id_people = niv0_2.id_collaborator)
43. 11,484.116 11,484.116 ↑ 1.0 1 1,640,588

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

  • Filter: (id = collaborator.id_constellation)
  • Rows Removed by Filter: 42
44. 9,843.528 9,843.528 ↑ 1.0 1 1,640,588

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,640,588)

  • Filter: (id = collaborator.id_constellation)
  • Rows Removed by Filter: 42