explain.depesz.com

PostgreSQL's explain analyze made readable

Result: laao

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 81,228.766 ↓ 5.0 5 1

Unique (cost=22,568.61..22,568.65 rows=1 width=144) (actual time=81,228.761..81,228.766 rows=5 loops=1)

2. 1.955 81,228.760 ↓ 9.0 9 1

Sort (cost=22,568.61..22,568.61 rows=1 width=144) (actual time=81,228.760..81,228.760 rows=9 loops=1)

  • Sort Key: films.nom_film, reservations.date_debut, cinema.liv_ville, cinema.liv_dep, cinema.id_cinema, cinema.nom_cinema, films.id_film, societe.id_societe, societe.nom_societe, prestataire.notif_emails, package_details.package_label, reservations.date_fin, reservations.comment_delivery, reser_delivery_date.cine_delivery_date
  • Sort Method: quicksort Memory: 29kB
3. 3.763 81,226.805 ↓ 9.0 9 1

Nested Loop Left Join (cost=384.08..22,568.60 rows=1 width=144) (actual time=8,758.408..81,226.805 rows=9 loops=1)

  • Filter: (((reser_delivery_date.cine_delivery_date >= '2020-09-09 00:00:00'::timestamp without time zone) OR (reservations.date_debut >= '2020-09-09'::date)) AND ((reser_delivery_date.cine_delivery_date < '2020-09-16 00:00:00'::timestamp without time zone) OR (reservations.date_debut < '2020-09-16'::date)))
  • Rows Removed by Filter: 11,071
4. 0.000 81,211.962 ↓ 11,080.0 11,080 1

Nested Loop (cost=383.79..22,568.28 rows=1 width=140) (actual time=8,592.813..81,211.962 rows=11,080 loops=1)

5. 0.000 81,198.206 ↓ 15,116.0 15,116 1

Nested Loop (cost=383.66..22,568.11 rows=1 width=144) (actual time=8,592.502..81,198.206 rows=15,116 loops=1)

6. 8.272 81,162.184 ↓ 15,878.0 47,634 1

Merge Join (cost=383.53..22,567.62 rows=3 width=148) (actual time=8,460.902..81,162.184 rows=47,634 loops=1)

  • Merge Cond: (requetes_transport.presta_dest = prestataire.id_societe)
7. 34,001.041 81,151.129 ↓ 15,878.0 47,634 1

Nested Loop (cost=379.83..33,655.43 rows=3 width=128) (actual time=8,460.704..81,151.129 rows=47,634 loops=1)

  • Join Filter: (requetes_transport.presta_dest = societe.id_societe)
  • Rows Removed by Join Filter: 461,099,678
8. 57.260 57.260 ↑ 1.0 4,871 1

Index Scan using societe_pkey on societe (cost=0.28..590.36 rows=4,909 width=25) (actual time=0.011..57.260 rows=4,871 loops=1)

9. 38,733.760 47,092.828 ↓ 23,668.0 94,672 4,871

Materialize (cost=379.54..32,770.54 rows=4 width=103) (actual time=0.005..9.668 rows=94,672 loops=4,871)

10. 19.221 8,359.068 ↓ 23,668.0 94,672 1

Nested Loop Left Join (cost=379.54..32,770.52 rows=4 width=103) (actual time=9.335..8,359.068 rows=94,672 loops=1)

  • Filter: (delivery.info IS NULL)
  • Rows Removed by Filter: 2
11. 21.952 6,919.977 ↓ 23,664.5 94,658 1

Nested Loop (cost=379.12..32,768.57 rows=4 width=107) (actual time=9.326..6,919.977 rows=94,658 loops=1)

12. 27.385 4,626.233 ↓ 23,664.5 94,658 1

Nested Loop (cost=378.69..32,766.36 rows=4 width=95) (actual time=9.209..4,626.233 rows=94,658 loops=1)

13. 38.798 3,741.112 ↓ 23,826.0 71,478 1

Nested Loop (cost=378.26..32,764.93 rows=3 width=95) (actual time=9.195..3,741.112 rows=71,478 loops=1)

14. 75.707 3,559.358 ↓ 23,826.0 71,478 1

Nested Loop (cost=377.98..32,764.00 rows=3 width=64) (actual time=9.190..3,559.358 rows=71,478 loops=1)

  • Join Filter: (ver_pkg.id_ver = reser_version_pref.id_ver)
  • Rows Removed by Join Filter: 168,173
15. 54.786 2,164.475 ↓ 6.2 164,897 1

Nested Loop (cost=377.55..18,504.62 rows=26,558 width=64) (actual time=9.182..2,164.475 rows=164,897 loops=1)

16. 31.343 702.239 ↓ 5.0 140,745 1

Nested Loop (cost=377.12..3,593.13 rows=28,206 width=50) (actual time=9.163..702.239 rows=140,745 loops=1)

  • Join Filter: (films.id_film = cine_film.id_film)
17. 0.173 21.246 ↓ 1.6 355 1

Nested Loop (cost=376.69..1,023.29 rows=222 width=46) (actual time=9.144..21.246 rows=355 loops=1)

18. 3.277 15.301 ↓ 1.2 444 1

Hash Join (cost=376.41..903.31 rows=363 width=46) (actual time=9.013..15.301 rows=444 loops=1)

  • Hash Cond: (package_details.id_film = films.id_film)
19. 3.202 3.202 ↑ 1.0 24,165 1

Seq Scan on package_details (cost=0.00..432.65 rows=24,165 width=24) (actual time=0.172..3.202 rows=24,165 loops=1)

20. 0.082 8.822 ↑ 1.0 203 1

Hash (cost=373.87..373.87 rows=203 width=22) (actual time=8.822..8.822 rows=203 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
21. 8.477 8.740 ↑ 1.0 203 1

Bitmap Heap Scan on films (cost=5.86..373.87 rows=203 width=22) (actual time=0.413..8.740 rows=203 loops=1)

  • Recheck Cond: (id_societe = 42)
  • Heap Blocks: exact=95
22. 0.263 0.263 ↑ 1.0 203 1

Bitmap Index Scan on idx_films_id_societe_advanced (cost=0.00..5.81 rows=203 width=0) (actual time=0.263..0.263 rows=203 loops=1)

  • Index Cond: (id_societe = 42)
23. 5.772 5.772 ↑ 1.0 1 444

Index Only Scan using ver_pkg_pkey on ver_pkg (cost=0.29..0.32 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=444)

  • Index Cond: (id_pkg = package_details.id_pkg)
  • Heap Fetches: 355
24. 649.650 649.650 ↓ 1.3 396 355

Index Scan using cine_film_id_film_key on cine_film (cost=0.43..7.89 rows=295 width=12) (actual time=0.057..1.830 rows=396 loops=355)

  • Index Cond: (id_film = package_details.id_film)
25. 1,407.450 1,407.450 ↑ 2.0 1 140,745

Index Scan using idx_reservations_id_cf on reservations (cost=0.43..0.51 rows=2 width=22) (actual time=0.008..0.010 rows=1 loops=140,745)

  • Index Cond: (id_cf = cine_film.id_cf)
  • Filter: ((NOT alt_format) AND is_valid)
  • Rows Removed by Filter: 0
26. 1,319.176 1,319.176 ↑ 2.0 1 164,897

Index Scan using idx_rvp_id_reser on reser_version_pref (cost=0.43..0.51 rows=2 width=8) (actual time=0.008..0.008 rows=1 loops=164,897)

  • Index Cond: (id_reser = reservations.id_reser)
27. 142.956 142.956 ↑ 1.0 1 71,478

Index Scan using cinema_id_cinema_area on cinema (cost=0.28..0.30 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=71,478)

  • Index Cond: (id_cinema = cine_film.id_cinema)
28. 857.736 857.736 ↑ 1.0 1 71,478

Index Scan using idx_requete_reser_transport_id_reser on requete_reser_transport (cost=0.43..0.47 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=71,478)

  • Index Cond: (id_reser = reser_version_pref.id_reser)
29. 2,271.792 2,271.792 ↑ 1.0 1 94,658

Index Scan using requetes_transport_pkey on requetes_transport (cost=0.43..0.54 rows=1 width=16) (actual time=0.024..0.024 rows=1 loops=94,658)

  • Index Cond: (id_transport = requete_reser_transport.id_transport)
30. 1,419.870 1,419.870 ↓ 0.0 0 94,658

Index Scan using idx_delivery_id_transport on delivery (cost=0.43..0.48 rows=1 width=17) (actual time=0.015..0.015 rows=0 loops=94,658)

  • Index Cond: (requetes_transport.id_transport = id_transport)
31. 2.612 2.783 ↓ 711.7 47,686 1

Sort (cost=3.70..3.87 rows=67 width=28) (actual time=0.194..2.783 rows=47,686 loops=1)

  • Sort Key: prestataire.id_societe
  • Sort Method: quicksort Memory: 30kB
32. 0.171 0.171 ↑ 1.0 67 1

Seq Scan on prestataire (cost=0.00..1.67 rows=67 width=28) (actual time=0.167..0.171 rows=67 loops=1)

33. 47.634 47.634 ↓ 0.0 0 47,634

Index Scan using type_envoi_transport_pkey on type_envoi_transport (cost=0.13..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=47,634)

  • Index Cond: (id_type = requetes_transport.type_envoi)
  • Filter: ((type_transport)::text = 'ENVOI_DD'::text)
  • Rows Removed by Filter: 1
34. 15.116 15.116 ↑ 1.0 1 15,116

Index Scan using transport_status_pkey on transport_status (cost=0.13..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=15,116)

  • Index Cond: (id_status = requetes_transport.status)
  • Filter: (((name)::text = 'VALIDATED'::text) OR ((name)::text = 'IN_PROGRESS'::text))
  • Rows Removed by Filter: 0
35. 11.080 11.080 ↓ 0.0 0 11,080

Index Scan using unique_reser on reser_delivery_date (cost=0.28..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=11,080)

  • Index Cond: (id_reser = reservations.id_reser)
Planning time : 16.455 ms
Execution time : 81,230.966 ms