By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
200 rows affected
1000 rows affected
5462 rows affected
| QUERY PLAN |
|---|
| Merge Left Join (cost=445.09..515.01 rows=1 width=51) (actual time=2.798..2.800 rows=1 loops=1) |
| Merge Cond: (film.film_id = film_actor.film_id) |
| -> Index Only Scan using idx_film_title_film_id on film (cost=0.28..4.29 rows=1 width=19) (actual time=0.018..0.019 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> GroupAggregate (cost=444.82..498.25 rows=997 width=36) (actual time=2.777..2.777 rows=1 loops=1) |
| Group Key: film_actor.film_id |
| -> Sort (cost=444.82..458.47 rows=5462 width=10) (actual time=2.766..2.767 rows=11 loops=1) |
| Sort Key: film_actor.film_id |
| Sort Method: quicksort Memory: 449kB |
| -> Hash Join (cost=6.50..105.76 rows=5462 width=10) (actual time=0.093..1.698 rows=5462 loops=1) |
| Hash Cond: (film_actor.actor_id = actor.actor_id) |
| -> Seq Scan on film_actor (cost=0.00..84.62 rows=5462 width=8) (actual time=0.007..0.390 rows=5462 loops=1) |
| -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.070..0.070 rows=200 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 17kB |
| -> Seq Scan on actor (cost=0.00..4.00 rows=200 width=10) (actual time=0.005..0.041 rows=200 loops=1) |
| Planning time: 0.443 ms |
| Execution time: 2.849 ms |
| QUERY PLAN |
|---|
| Nested Loop Left Join (cost=9.26..13.31 rows=1 width=51) (actual time=0.083..0.084 rows=1 loops=1) |
| -> Index Only Scan using idx_film_title_film_id on film (cost=0.28..4.29 rows=1 width=19) (actual time=0.009..0.010 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> Aggregate (cost=8.98..8.99 rows=1 width=32) (actual time=0.072..0.072 rows=1 loops=1) |
| -> Hash Join (cost=4.43..8.97 rows=5 width=6) (actual time=0.035..0.065 rows=10 loops=1) |
| Hash Cond: (actor.actor_id = film_actor.actor_id) |
| -> Seq Scan on actor (cost=0.00..4.00 rows=200 width=10) (actual time=0.008..0.020 rows=200 loops=1) |
| -> Hash (cost=4.37..4.37 rows=5 width=4) (actual time=0.014..0.014 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Index Only Scan using film_actor_pkey on film_actor (cost=0.28..4.37 rows=5 width=4) (actual time=0.009..0.011 rows=10 loops=1) |
| Index Cond: (film_id = film.film_id) |
| Heap Fetches: 0 |
| Planning time: 0.219 ms |
| Execution time: 0.114 ms |
| QUERY PLAN |
|---|
| GroupAggregate (cost=0.70..11.52 rows=1 width=51) (actual time=0.055..0.055 rows=1 loops=1) |
| Group Key: film.film_id |
| -> Nested Loop Left Join (cost=0.70..11.48 rows=5 width=25) (actual time=0.013..0.047 rows=10 loops=1) |
| -> Index Only Scan using idx_film_title_film_id on film (cost=0.28..4.29 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> Nested Loop (cost=0.43..5.19 rows=200 width=10) (actual time=0.005..0.038 rows=10 loops=1) |
| -> Index Only Scan using film_actor_pkey on film_actor (cost=0.28..4.37 rows=5 width=8) (actual time=0.002..0.023 rows=10 loops=1) |
| Index Cond: (film_id = film.film_id) |
| Heap Fetches: 0 |
| -> Index Only Scan using idx_actor_first_name_actor_id on actor (cost=0.14..0.16 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=10) |
| Index Cond: (actor_id = film_actor.actor_id) |
| Heap Fetches: 0 |
| Planning time: 0.269 ms |
| Execution time: 0.081 ms |
| QUERY PLAN |
|---|
| GroupAggregate (cost=0.70..11.52 rows=1 width=51) (actual time=0.030..0.031 rows=1 loops=1) |
| Group Key: film.film_id |
| -> Nested Loop Left Join (cost=0.70..11.48 rows=5 width=25) (actual time=0.012..0.024 rows=10 loops=1) |
| -> Index Only Scan using idx_film_title_film_id on film (cost=0.28..4.29 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> Nested Loop (cost=0.43..5.19 rows=200 width=10) (actual time=0.005..0.015 rows=10 loops=1) |
| -> Index Only Scan using film_actor_pkey on film_actor (cost=0.28..4.37 rows=5 width=8) (actual time=0.002..0.004 rows=10 loops=1) |
| Index Cond: (film_id = film.film_id) |
| Heap Fetches: 0 |
| -> Index Only Scan using idx_actor_first_name_actor_id on actor (cost=0.14..0.16 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=10) |
| Index Cond: (actor_id = film_actor.actor_id) |
| Heap Fetches: 0 |
| Planning time: 0.256 ms |
| Execution time: 0.055 ms |
| QUERY PLAN |
|---|
| Nested Loop Left Join (cost=9.24..13.29 rows=1 width=51) (actual time=0.073..0.074 rows=1 loops=1) |
| -> Index Only Scan using idx_film_title_film_id on film f (cost=0.28..4.29 rows=1 width=19) (actual time=0.007..0.008 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> Result (cost=8.97..8.98 rows=1 width=32) (actual time=0.065..0.065 rows=1 loops=1) |
| InitPlan 1 (returns $1) |
| -> Hash Join (cost=4.43..8.97 rows=5 width=6) (actual time=0.020..0.049 rows=10 loops=1) |
| Hash Cond: (a.actor_id = fa.actor_id) |
| -> Seq Scan on actor a (cost=0.00..4.00 rows=200 width=10) (actual time=0.007..0.018 rows=200 loops=1) |
| -> Hash (cost=4.37..4.37 rows=5 width=4) (actual time=0.008..0.008 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Index Only Scan using film_actor_pkey on film_actor fa (cost=0.28..4.37 rows=5 width=4) (actual time=0.004..0.005 rows=10 loops=1) |
| Index Cond: (film_id = $0) |
| Heap Fetches: 0 |
| Planning time: 0.207 ms |
| Execution time: 0.103 ms |
| QUERY PLAN |
|---|
| Index Only Scan using idx_film_title_film_id on film f (cost=0.28..13.26 rows=1 width=51) (actual time=0.075..0.075 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| SubPlan 1 |
| -> Hash Join (cost=4.43..8.97 rows=5 width=6) (actual time=0.027..0.055 rows=10 loops=1) |
| Hash Cond: (a.actor_id = fa.actor_id) |
| -> Seq Scan on actor a (cost=0.00..4.00 rows=200 width=10) (actual time=0.006..0.018 rows=200 loops=1) |
| -> Hash (cost=4.37..4.37 rows=5 width=4) (actual time=0.014..0.014 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Index Only Scan using film_actor_pkey on film_actor fa (cost=0.28..4.37 rows=5 width=4) (actual time=0.010..0.012 rows=10 loops=1) |
| Index Cond: (film_id = f.film_id) |
| Heap Fetches: 0 |
| Planning time: 0.169 ms |
| Execution time: 0.096 ms |
| QUERY PLAN |
|---|
| GroupAggregate (cost=0.70..9.57 rows=1 width=51) (actual time=0.031..0.031 rows=1 loops=1) |
| Group Key: f.film_id |
| -> Nested Loop Left Join (cost=0.70..9.53 rows=5 width=25) (actual time=0.013..0.024 rows=10 loops=1) |
| -> Nested Loop Left Join (cost=0.56..8.71 rows=5 width=23) (actual time=0.011..0.013 rows=10 loops=1) |
| -> Index Only Scan using idx_film_title_film_id on film f (cost=0.28..4.29 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> Index Only Scan using film_actor_pkey on film_actor fa (cost=0.28..4.37 rows=5 width=8) (actual time=0.003..0.004 rows=10 loops=1) |
| Index Cond: (film_id = f.film_id) |
| Heap Fetches: 0 |
| -> Index Only Scan using idx_actor_first_name_actor_id on actor a (cost=0.14..0.16 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=10) |
| Index Cond: (actor_id = fa.actor_id) |
| Heap Fetches: 0 |
| Planning time: 0.245 ms |
| Execution time: 0.058 ms |
| QUERY PLAN |
|---|
| Merge Left Join (cost=445.09..515.01 rows=1 width=51) (actual time=2.668..2.670 rows=1 loops=1) |
| Merge Cond: (f.film_id = fa.film_id) |
| -> Index Only Scan using idx_film_title_film_id on film f (cost=0.28..4.29 rows=1 width=19) (actual time=0.006..0.008 rows=1 loops=1) |
| Index Cond: (title = 'ACADEMY DINOSAUR'::text) |
| Heap Fetches: 0 |
| -> GroupAggregate (cost=444.82..498.25 rows=997 width=36) (actual time=2.660..2.660 rows=1 loops=1) |
| Group Key: fa.film_id |
| -> Sort (cost=444.82..458.47 rows=5462 width=10) (actual time=2.650..2.651 rows=11 loops=1) |
| Sort Key: fa.film_id |
| Sort Method: quicksort Memory: 449kB |
| -> Hash Join (cost=6.50..105.76 rows=5462 width=10) (actual time=0.060..1.629 rows=5462 loops=1) |
| Hash Cond: (fa.actor_id = actor.actor_id) |
| -> Seq Scan on film_actor fa (cost=0.00..84.62 rows=5462 width=8) (actual time=0.008..0.381 rows=5462 loops=1) |
| -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.047..0.047 rows=200 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 17kB |
| -> Seq Scan on actor (cost=0.00..4.00 rows=200 width=10) (actual time=0.005..0.023 rows=200 loops=1) |
| Planning time: 0.191 ms |
| Execution time: 2.699 ms |