explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uAnH

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 8,708.799 ↓ 5.0 5 1

Unique (cost=22,587.39..22,587.43 rows=1 width=144) (actual time=8,708.794..8,708.799 rows=5 loops=1)

2. 0.200 8,708.794 ↓ 9.0 9 1

Sort (cost=22,587.39..22,587.39 rows=1 width=144) (actual time=8,708.793..8,708.794 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. 5.896 8,708.594 ↓ 9.0 9 1

Nested Loop Left Join (cost=380.64..22,587.38 rows=1 width=144) (actual time=7,602.498..8,708.594 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. 3.214 8,691.618 ↓ 11,080.0 11,080 1

Nested Loop Left Join (cost=380.36..22,587.06 rows=1 width=140) (actual time=7,465.616..8,691.618 rows=11,080 loops=1)

  • Filter: (delivery.info IS NULL)
5. 0.366 8,488.964 ↓ 11,080.0 11,080 1

Nested Loop (cost=379.93..22,586.57 rows=1 width=144) (actual time=7,465.265..8,488.964 rows=11,080 loops=1)

6. 0.000 8,476.377 ↓ 12,221.0 12,221 1

Nested Loop (cost=379.80..22,586.40 rows=1 width=148) (actual time=7,449.830..8,476.377 rows=12,221 loops=1)

7. 10.314 8,435.658 ↓ 47,620.0 47,620 1

Merge Join (cost=379.66..22,586.24 rows=1 width=152) (actual time=197.194..8,435.658 rows=47,620 loops=1)

  • Merge Cond: (requetes_transport.presta_dest = societe.id_societe)
8. 504.923 8,405.526 ↓ 15,873.3 47,620 1

Nested Loop (cost=379.26..32,785.74 rows=3 width=135) (actual time=197.181..8,405.526 rows=47,620 loops=1)

  • Join Filter: (requetes_transport.presta_dest = prestataire.id_societe)
  • Rows Removed by Join Filter: 6,294,466
9. 0.365 0.365 ↑ 1.0 67 1

Index Scan using prestataire_pkey on prestataire (cost=0.14..13.15 rows=67 width=28) (actual time=0.009..0.365 rows=67 loops=1)

10. 622.053 7,900.238 ↓ 23,664.5 94,658 67

Materialize (cost=379.12..32,768.59 rows=4 width=107) (actual time=0.157..117.914 rows=94,658 loops=67)

11. 66.459 7,278.185 ↓ 23,664.5 94,658 1

Nested Loop (cost=379.12..32,768.57 rows=4 width=107) (actual time=10.014..7,278.185 rows=94,658 loops=1)

12. 5.462 4,845.276 ↓ 23,664.5 94,658 1

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

13. 49.194 3,910.600 ↓ 23,826.0 71,478 1

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

14. 116.046 3,718.450 ↓ 23,826.0 71,478 1

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

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

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

16. 32.842 739.054 ↓ 5.0 140,745 1

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

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

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

18. 3.828 16.132 ↓ 1.2 444 1

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

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

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

20. 0.093 9.035 ↑ 1.0 203 1

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

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

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

  • Recheck Cond: (id_societe = 42)
  • Heap Blocks: exact=95
22. 0.461 0.461 ↑ 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.461..0.461 rows=203 loops=1)

  • Index Cond: (id_societe = 42)
23. 6.216 6.216 ↑ 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.013..0.014 rows=1 loops=444)

  • Index Cond: (id_pkg = package_details.id_pkg)
  • Heap Fetches: 355
24. 683.375 683.375 ↓ 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.072..1.925 rows=396 loops=355)

  • Index Cond: (id_film = package_details.id_film)
25. 1,548.195 1,548.195 ↑ 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.009..0.011 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. 929.214 929.214 ↑ 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.012..0.013 rows=1 loops=71,478)

  • Index Cond: (id_reser = reser_version_pref.id_reser)
29. 2,366.450 2,366.450 ↑ 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.025..0.025 rows=1 loops=94,658)

  • Index Cond: (id_transport = requete_reser_transport.id_transport)
30. 19.818 19.818 ↓ 10.4 51,204 1

Index Scan using societe_pkey on societe (cost=0.28..590.36 rows=4,909 width=25) (actual time=0.006..19.818 rows=51,204 loops=1)

31. 47.620 47.620 ↓ 0.0 0 47,620

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=0 loops=47,620)

  • Index Cond: (id_status = requetes_transport.status)
  • Filter: (((name)::text = 'VALIDATED'::text) OR ((name)::text = 'IN_PROGRESS'::text))
  • Rows Removed by Filter: 1
32. 12.221 12.221 ↑ 1.0 1 12,221

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=1 loops=12,221)

  • Index Cond: (id_type = requetes_transport.type_envoi)
  • Filter: ((type_transport)::text = 'ENVOI_DD'::text)
  • Rows Removed by Filter: 0
33. 199.440 199.440 ↓ 0.0 0 11,080

Index Scan using idx_delivery_id_transport on delivery (cost=0.43..0.48 rows=1 width=17) (actual time=0.018..0.018 rows=0 loops=11,080)

  • Index Cond: (requetes_transport.id_transport = id_transport)
34. 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 : 43.797 ms
Execution time : 8,712.933 ms