explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K9VZ

Settings
# exclusive inclusive rows x rows loops node
1. 3,774.483 69,974.138 ↑ 8.0 200,170 1

GroupAggregate (cost=505,564.79..210,468,240.71 rows=1,595,756 width=546) (actual time=24,357.238..69,974.138 rows=200,170 loops=1)

  • Group Key: m.id
2. 643.315 28,167.355 ↓ 1.0 2,455,946 1

Merge Left Join (cost=505,564.79..805,520.18 rows=2,427,220 width=518) (actual time=24,355.990..28,167.355 rows=2,455,946 loops=1)

  • Merge Cond: (m.id = mhp.id_movie)
3. 1,247.060 1,247.060 ↑ 8.0 200,170 1

Index Scan using movie_pkey on movie m (cost=0.43..253,490.08 rows=1,595,756 width=439) (actual time=0.005..1,247.060 rows=200,170 loops=1)

4. 251.955 26,276.980 ↑ 1.0 2,427,220 1

Materialize (cost=505,564.36..517,700.46 rows=2,427,220 width=87) (actual time=24,355.980..26,276.980 rows=2,427,220 loops=1)

5. 2,792.470 26,025.025 ↑ 1.0 2,427,220 1

Sort (cost=505,564.36..511,632.41 rows=2,427,220 width=87) (actual time=24,355.977..26,025.025 rows=2,427,220 loops=1)

  • Sort Key: mhp.id_movie
  • Sort Method: external merge Disk: 192504kB
6. 1,292.132 23,232.555 ↑ 1.0 2,427,220 1

Hash Left Join (cost=54,660.15..180,117.83 rows=2,427,220 width=87) (actual time=496.924..23,232.555 rows=2,427,220 loops=1)

  • Hash Cond: (mhp.id_person = p.id)
7. 21,447.258 21,447.258 ↑ 1.0 2,427,220 1

Seq Scan on movie_has_person mhp (cost=0.00..119,086.20 rows=2,427,220 width=60) (actual time=0.017..21,447.258 rows=2,427,220 loops=1)

8. 217.762 493.165 ↑ 1.0 745,162 1

Hash (cost=45,345.62..45,345.62 rows=745,162 width=43) (actual time=493.165..493.165 rows=745,162 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 50779kB
9. 275.403 275.403 ↑ 1.0 745,162 1

Seq Scan on person p (cost=0.00..45,345.62 rows=745,162 width=43) (actual time=0.023..275.403 rows=745,162 loops=1)

10.          

SubPlan (for GroupAggregate)

11. 0.000 10,809.180 ↑ 1.0 1 200,170

Limit (cost=6.01..6.02 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=200,170)

12. 200.170 10,809.180 ↑ 1.0 1 200,170

Aggregate (cost=6.01..6.02 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=200,170)

13. 10,609.010 10,609.010 ↑ 1.0 1 200,170

Index Scan using movie_has_release_id_movie_idx on movie_has_release mhr (cost=0.43..6.01 rows=1 width=10) (actual time=0.017..0.053 rows=1 loops=200,170)

  • Index Cond: (id_movie = m.id)
  • Filter: (((tags)::ltree[] ~ 'Release.Type.Theater'::lquery) AND (NOT ((tags)::ltree[] ~ 'Generic.Flag.NotReleasedYet'::lquery)) AND (NOT ((tags)::ltree[] ~ 'Theater.Flag.Preview'::lquery)) AND (NOT ((tags)::ltree[] ~ 'Theater.Limitation.*'::lquery)))
  • Rows Removed by Filter: 1
14. 600.510 1,401.190 ↑ 1.0 1 200,170

Aggregate (cost=5.03..5.04 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=200,170)

15. 220.167 800.680 ↑ 1.0 1 200,170

Nested Loop (cost=0.56..5.02 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=200,170)

16. 400.340 400.340 ↑ 1.0 1 200,170

Index Only Scan using movie_has_country_pkey on movie_has_country mhc (cost=0.42..2.64 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=200,170)

  • Index Cond: (id_movie = m.id)
  • Heap Fetches: 5502
17. 180.173 180.173 ↑ 1.0 1 180,173

Index Scan using country_pkey on country c (cost=0.14..2.36 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=180,173)

  • Index Cond: (id = mhc.id_country)
18. 1,000.850 16,614.110 ↑ 1.0 1 200,170

Aggregate (cost=56.16..56.17 rows=1 width=32) (actual time=0.083..0.083 rows=1 loops=200,170)

19. 324.800 15,613.260 ↑ 1.8 11 200,170

Nested Loop (cost=0.98..55.96 rows=20 width=79) (actual time=0.011..0.078 rows=11 loops=200,170)

20. 8,006.800 8,006.800 ↑ 1.7 12 200,170

Index Only Scan using movie_has_person_id_movie_id_person_activity_key on movie_has_person mhp_1 (cost=0.56..3.11 rows=20 width=52) (actual time=0.007..0.040 rows=12 loops=200,170)

  • Index Cond: (id_movie = m.id)
  • Heap Fetches: 713
21. 7,281.660 7,281.660 ↑ 1.0 1 2,427,220

Index Scan using person_pkey on person p_1 (cost=0.42..2.64 rows=1 width=43) (actual time=0.003..0.003 rows=1 loops=2,427,220)

  • Index Cond: (id = mhp_1.id_person)
22. 1,801.530 7,606.460 ↑ 1.0 1 200,170

Aggregate (cost=56.16..56.17 rows=1 width=32) (actual time=0.038..0.038 rows=1 loops=200,170)

23. 149.810 5,804.930 ↑ 1.8 11 200,170

Nested Loop (cost=0.98..55.96 rows=20 width=79) (actual time=0.005..0.029 rows=11 loops=200,170)

24. 800.680 800.680 ↑ 1.7 12 200,170

Index Only Scan using movie_has_person_id_movie_id_person_activity_key on movie_has_person mhp_2 (cost=0.56..3.11 rows=20 width=52) (actual time=0.002..0.004 rows=12 loops=200,170)

  • Index Cond: (id_movie = m.id)
  • Heap Fetches: 713
25. 4,854.440 4,854.440 ↑ 1.0 1 2,427,220

Index Scan using person_pkey on person p_2 (cost=0.42..2.64 rows=1 width=43) (actual time=0.002..0.002 rows=1 loops=2,427,220)

  • Index Cond: (id = mhp_2.id_person)
26. 600.510 1,601.360 ↑ 1.0 1 200,170

Aggregate (cost=7.69..7.70 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=200,170)

27. 168.730 1,000.850 ↑ 2.0 1 200,170

Nested Loop (cost=0.71..7.68 rows=2 width=18) (actual time=0.004..0.005 rows=1 loops=200,170)

28. 600.510 600.510 ↑ 2.0 1 200,170

Index Only Scan using movie_has_company_pkey on movie_has_company mhc_1 (cost=0.42..2.66 rows=2 width=8) (actual time=0.002..0.003 rows=1 loops=200,170)

  • Index Cond: (id_movie = m.id)
  • Filter: ((activity)::ltree = ANY ('{Activity.Company.CoProduction,Activity.Company.ExecutiveProducer,Activity.Company.Production}'::ltree[]))
  • Rows Removed by Filter: 0
  • Heap Fetches: 57
29. 231.610 231.610 ↑ 1.0 1 115,805

Index Scan using company_pkey on company c_1 (cost=0.29..2.51 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=115,805)

  • Index Cond: (id = mhc_1.id_company)
Planning time : 8.349 ms
Execution time : 70,011.515 ms