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?.
CREATE SEQUENCE
CREATE TABLE
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 200
INSERT 0 1000
INSERT 0 5462
VACUUM
| QUERY PLAN |
|---|
| Merge Left Join (cost=453.12..519.03 rows=1 width=51) (actual time=2.994..2.995 rows=1 loops=1) |
| Merge Cond: (film.film_id = film_actor.film_id) |
| -> Sort (cost=8.30..8.31 rows=1 width=19) (actual time=0.196..0.196 rows=1 loops=1) |
| Sort Key: film.film_id |
| Sort Method: quicksort Memory: 25kB |
| -> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.013..0.016 rows=1 loops=1) |
| Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text) |
| -> GroupAggregate (cost=444.82..498.25 rows=997 width=34) (actual time=2.791..2.792 rows=1 loops=1) |
| Group Key: film_actor.film_id |
| -> Sort (cost=444.82..458.47 rows=5462 width=8) (actual time=2.771..2.772 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=8) (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=4) (actual time=0.008..0.405 rows=5462 loops=1) |
| -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.064..0.064 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.006..0.031 rows=200 loops=1) |
| Planning time: 2.546 ms |
| Execution time: 3.315 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Nested Loop Left Join (cost=23.15..31.20 rows=1 width=51) (actual time=0.544..0.546 rows=1 loops=1) |
| -> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.012..0.013 rows=1 loops=1) |
| Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text) |
| -> Aggregate (cost=22.87..22.88 rows=1 width=32) (actual time=0.530..0.530 rows=1 loops=1) |
| -> Hash Join (cost=18.32..22.86 rows=5 width=6) (actual time=0.467..0.518 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.007..0.023 rows=200 loops=1) |
| -> Hash (cost=18.26..18.26 rows=5 width=2) (actual time=0.415..0.415 rows=10 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| -> Bitmap Heap Scan on film_actor (cost=4.32..18.26 rows=5 width=2) (actual time=0.380..0.401 rows=10 loops=1) |
| Recheck Cond: (film_id = film.film_id) |
| Heap Blocks: exact=10 |
| -> Bitmap Index Scan on idx_fk_film_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.320..0.320 rows=10 loops=1) |
| Index Cond: (film_id = film.film_id) |
| Planning time: 0.739 ms |
| Execution time: 0.631 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=29.44..29.49 rows=1 width=51) (actual time=0.158..0.160 rows=1 loops=1) |
| Group Key: film.film_id |
| -> Sort (cost=29.44..29.45 rows=5 width=25) (actual time=0.144..0.147 rows=10 loops=1) |
| Sort Key: film.film_id |
| Sort Method: quicksort Memory: 25kB |
| -> Nested Loop Left Join (cost=4.74..29.38 rows=5 width=25) (actual time=0.074..0.133 rows=10 loops=1) |
| -> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.033..0.034 rows=1 loops=1) |
| Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text) |
| -> Nested Loop (cost=4.47..19.09 rows=200 width=8) (actual time=0.039..0.094 rows=10 loops=1) |
| -> Bitmap Heap Scan on film_actor (cost=4.32..18.26 rows=5 width=4) (actual time=0.028..0.042 rows=10 loops=1) |
| Recheck Cond: (film_id = film.film_id) |
| Heap Blocks: exact=10 |
| -> Bitmap Index Scan on idx_fk_film_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.022..0.022 rows=10 loops=1) |
| Index Cond: (film_id = film.film_id) |
| -> Index Scan using actor_pkey on actor (cost=0.14..0.17 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=10) |
| Index Cond: (actor_id = film_actor.actor_id) |
| Planning time: 0.739 ms |
| Execution time: 0.247 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=29.44..29.49 rows=1 width=51) (actual time=0.076..0.077 rows=1 loops=1) |
| Group Key: film.film_id |
| -> Sort (cost=29.44..29.45 rows=5 width=25) (actual time=0.070..0.071 rows=10 loops=1) |
| Sort Key: film.film_id |
| Sort Method: quicksort Memory: 25kB |
| -> Nested Loop Left Join (cost=4.74..29.38 rows=5 width=25) (actual time=0.036..0.064 rows=10 loops=1) |
| -> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.008..0.009 rows=1 loops=1) |
| Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text) |
| -> Nested Loop (cost=4.47..19.09 rows=200 width=8) (actual time=0.027..0.052 rows=10 loops=1) |
| -> Bitmap Heap Scan on film_actor (cost=4.32..18.26 rows=5 width=4) (actual time=0.019..0.026 rows=10 loops=1) |
| Recheck Cond: (film_id = film.film_id) |
| Heap Blocks: exact=10 |
| -> Bitmap Index Scan on idx_fk_film_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.015..0.015 rows=10 loops=1) |
| Index Cond: (film_id = film.film_id) |
| -> Index Scan using actor_pkey on actor (cost=0.14..0.17 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=10) |
| Index Cond: (actor_id = film_actor.actor_id) |
| Planning time: 0.339 ms |
| Execution time: 0.131 ms |
EXPLAIN