explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Inoi : Hard query on Join Order Benchmark, SQL Hash: -1864849711, Plan Hash: -736938986, optimized by AQO

Settings
# exclusive inclusive rows x rows loops node
1. 802.595 13,967.762 ↑ 1.0 1 1

Aggregate (cost=610,672.14..610,672.15 rows=1 width=64) (actual time=13,967.762..13,967.762 rows=1 loops=1)

  • Output: min(n.name), min(t.title)
  • Buffers: shared hit=954393
2. 1,234.586 13,165.167 ↑ 1.0 1,760,645 1

Hash Join (cost=424,724.89..601,868.92 rows=1,760,645 width=32) (actual time=10,580.439..13,165.167 rows=1,760,645 loops=1)

  • Output: n.name, t.title
  • Hash Cond: (mi.movie_id = t.id)
  • Buffers: shared hit=954393
3. 640.506 1,828.884 ↑ 1.0 3,036,719 1

Nested Loop (cost=3,159.86..165,721.50 rows=3,036,719 width=4) (actual time=474.571..1,828.884 rows=3,036,719 loops=1)

  • Output: mi.movie_id
  • Buffers: shared hit=35960
4. 0.036 0.036 ↑ 1.0 1 1

Seq Scan on public.info_type it (cost=0.00..2.41 rows=1 width=4) (actual time=0.013..0.036 rows=1 loops=1)

  • Output: it.id, it.info
  • Filter: ((it.info)::text = 'release dates'::text)
  • Rows Removed by Filter: 112
  • Buffers: shared hit=1
5. 720.575 1,188.342 ↑ 1.0 3,036,719 1

Bitmap Heap Scan on public.movie_info mi (cost=3,159.86..135,351.90 rows=3,036,719 width=8) (actual time=474.553..1,188.342 rows=3,036,719 loops=1)

  • Output: mi.id, mi.movie_id, mi.info_type_id, mi.info, mi.note
  • Recheck Cond: (mi.info_type_id = it.id)
  • Heap Blocks: exact=25448
  • Buffers: shared hit=35959
6. 467.767 467.767 ↓ 14.3 3,036,719 1

Bitmap Index Scan on info_type_id_movie_info (cost=0.00..2,400.68 rows=211,939 width=0) (actual time=467.767..467.767 rows=3,036,719 loops=1)

  • Index Cond: (mi.info_type_id = it.id)
  • Buffers: shared hit=10511
7. 127.069 10,101.697 ↑ 1.0 265,983 1

Hash (cost=418,240.25..418,240.25 rows=265,983 width=44) (actual time=10,101.697..10,101.697 rows=265,983 loops=1)

  • Output: ci.movie_id, mc.movie_id, n.name, t.title, t.id
  • Buckets: 524288 Batches: 1 Memory Usage: 26123kB
  • Buffers: shared hit=918433
8. 310.922 9,974.628 ↑ 1.0 265,983 1

Hash Join (cost=398,270.79..418,240.25 rows=265,983 width=44) (actual time=9,638.444..9,974.628 rows=265,983 loops=1)

  • Output: ci.movie_id, mc.movie_id, n.name, t.title, t.id
  • Hash Cond: (an.person_id = n.id)
  • Buffers: shared hit=918433
9. 181.304 181.304 ↓ 1.3 901,343 1

Index Only Scan using person_id_aka_name on public.aka_name an (cost=0.42..17,284.77 rows=674,055 width=4) (actual time=0.026..181.304 rows=901,343 loops=1)

  • Output: an.person_id
  • Heap Fetches: 0
  • Buffers: shared hit=3409
10. 68.190 9,482.402 ↑ 1.0 97,320 1

Hash (cost=397,053.86..397,053.86 rows=97,320 width=52) (actual time=9,482.402..9,482.402 rows=97,320 loops=1)

  • Output: ci.movie_id, ci.person_id, mc.movie_id, n.name, n.id, t.title, t.id
  • Buckets: 131072 Batches: 1 Memory Usage: 9935kB
  • Buffers: shared hit=915024
11. 131.104 9,414.212 ↑ 1.0 97,320 1

Nested Loop (cost=207,024.57..397,053.86 rows=97,320 width=52) (actual time=6,452.283..9,414.212 rows=97,320 loops=1)

  • Output: ci.movie_id, ci.person_id, mc.movie_id, n.name, n.id, t.title, t.id
  • Inner Unique: true
  • Buffers: shared hit=915024
12. 111.669 8,796.178 ↑ 1.0 97,386 1

Nested Loop (cost=207,024.14..322,943.34 rows=97,386 width=33) (actual time=6,452.262..8,796.178 rows=97,386 loops=1)

  • Output: ci.movie_id, ci.person_id, mc.movie_id, t.title, t.id
  • Inner Unique: true
  • Buffers: shared hit=525110
13. 372.475 8,266.945 ↑ 1.0 104,391 1

Hash Join (cost=207,023.71..262,886.97 rows=104,391 width=37) (actual time=6,452.232..8,266.945 rows=104,391 loops=1)

  • Output: ci.movie_id, ci.person_id, ci.person_role_id, mc.movie_id, t.title, t.id
  • Hash Cond: (mc.movie_id = t.id)
  • Buffers: shared hit=217912
14. 1,083.000 1,546.090 ↓ 1.2 1,153,798 1

Hash Join (cost=7,002.39..58,732.76 rows=950,335 width=4) (actual time=102.888..1,546.090 rows=1,153,798 loops=1)

  • Output: mc.movie_id
  • Inner Unique: true
  • Hash Cond: (mc.company_id = cn.id)
  • Buffers: shared hit=43289
15. 360.864 360.864 ↑ 1.0 2,609,129 1

Seq Scan on public.movie_companies mc (cost=0.00..44,881.29 rows=2,609,129 width=8) (actual time=0.167..360.864 rows=2,609,129 loops=1)

  • Output: mc.id, mc.movie_id, mc.company_id, mc.company_type_id, mc.note
  • Buffers: shared hit=37478
16. 26.379 102.226 ↑ 1.0 84,843 1

Hash (cost=5,932.46..5,932.46 rows=85,594 width=4) (actual time=102.226..102.226 rows=84,843 loops=1)

  • Output: cn.id
  • Buckets: 131072 Batches: 1 Memory Usage: 4007kB
  • Buffers: shared hit=5811
17. 75.847 75.847 ↑ 1.0 84,843 1

Seq Scan on public.company_name cn (cost=0.00..5,932.46 rows=85,594 width=4) (actual time=0.157..75.847 rows=84,843 loops=1)

  • Output: cn.id
  • Filter: ((cn.country_code)::text = '[us]'::text)
  • Rows Removed by Filter: 150154
  • Buffers: shared hit=5811
18. 101.681 6,348.380 ↓ 1.1 168,407 1

Hash (cost=198,128.65..198,128.65 rows=151,414 width=33) (actual time=6,348.380..6,348.380 rows=168,407 loops=1)

  • Output: ci.movie_id, ci.person_id, ci.person_role_id, t.title, t.id
  • Buckets: 262144 Batches: 1 Memory Usage: 14124kB
  • Buffers: shared hit=174623
19. 207.888 6,246.699 ↓ 1.1 168,407 1

Hash Join (cost=84,929.95..198,128.65 rows=151,414 width=33) (actual time=1,532.311..6,246.699 rows=168,407 loops=1)

  • Output: ci.movie_id, ci.person_id, ci.person_role_id, t.title, t.id
  • Inner Unique: true
  • Hash Cond: (ci.movie_id = t.id)
  • Buffers: shared hit=174623
20. 4,514.338 4,514.350 ↑ 1.0 276,166 1

Nested Loop (cost=0.56..112,474.32 rows=276,166 width=12) (actual time=0.417..4,514.350 rows=276,166 loops=1)

  • Output: ci.movie_id, ci.person_id, ci.person_role_id
  • Buffers: shared hit=138369
  • -> Index Scan using role_id_cast_info on public.cast_info ci (cost=0.56..109711.51 rows=276166 width=16) (actual time=0.406..4458.032 rows=276
21. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on public.role_type rt (cost=0.00..1.15 rows=1 width=4) (actual time=0.006..0.012 rows=1 loops=1)

  • Output: rt.id, rt.role
  • Filter: ((rt.role)::text = 'actress'::text)
  • Rows Removed by Filter: 11
  • Buffers: shared hit=1
  • Output: ci.id, ci.person_id, ci.movie_id, ci.person_role_id, ci.note, ci.nr_order, ci.role_id
  • Index Cond: (ci.role_id = rt.id)
  • Filter: (ci.note = ANY ('{(voice),"(voice: Japanese version)","(voice) (uncredited)","(voice: English version)"}'::text[]))
  • Rows Removed by Filter: 7175807
  • Buffers: shared hit=138368
22. 618.130 1,524.461 ↑ 1.0 1,381,453 1

Hash (cost=67,601.90..67,601.90 rows=1,386,199 width=21) (actual time=1,524.461..1,524.461 rows=1,381,453 loops=1)

  • Output: t.title, t.id
  • Buckets: 2097152 Batches: 1 Memory Usage: 90381kB
  • Buffers: shared hit=36254
23. 906.331 906.331 ↑ 1.0 1,381,453 1

Seq Scan on public.title t (cost=0.00..67,601.90 rows=1,386,199 width=21) (actual time=0.147..906.331 rows=1,381,453 loops=1)

  • Output: t.title, t.id
  • Filter: (t.production_year > 2000)
  • Rows Removed by Filter: 1146859
  • Buffers: shared hit=36254
24. 417.564 417.564 ↑ 1.0 1 104,391

Index Only Scan using char_name_pkey on public.char_name chn (cost=0.43..0.58 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=104,391)

  • Output: chn.id
  • Index Cond: (chn.id = ci.person_role_id)
  • Heap Fetches: 0
  • Buffers: shared hit=307198
25. 486.930 486.930 ↑ 1.0 1 97,386

Index Scan using name_pkey on public.name n (cost=0.43..0.76 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=97,386)

  • Output: n.id, n.name, n.imdb_index, n.imdb_id, n.gender, n.name_pcode_cf, n.name_pcode_nf, n.surname_pcode, n.md5sum
  • Index Cond: (n.id = ci.person_id)
  • Filter: ((n.gender)::text = 'f'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=389914