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?.
1111 rows affected
111111 rows affected
108 rows affected
10010 rows affected
296 rows affected
27182 rows affected
| QUERY PLAN |
|---|
| HashAggregate (cost=2546.77..2556.80 rows=1003 width=12) (actual time=70.591..70.792 rows=1003 loops=1) |
| Group Key: m.master_id |
| Batches: 1 Memory Usage: 193kB |
| -> Hash Join (cost=47.57..2095.65 rows=90224 width=4) (actual time=0.559..51.695 rows=90224 loops=1) |
| Hash Cond: (s.master_id = m.master_id) |
| -> Seq Scan on slave s (cost=0.00..1810.24 rows=90224 width=4) (actual time=0.037..26.765 rows=90224 loops=1) |
| -> Hash (cost=35.03..35.03 rows=1003 width=4) (actual time=0.495..0.497 rows=1003 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 44kB |
| -> Seq Scan on master m (cost=0.00..35.03 rows=1003 width=4) (actual time=0.010..0.335 rows=1003 loops=1) |
| Planning Time: 1.371 ms |
| Execution Time: 71.097 ms |
| QUERY PLAN |
|---|
| Hash Join (cost=2293.96..2331.63 rows=1003 width=12) (actual time=30.087..30.534 rows=1003 loops=1) |
| Hash Cond: (m.master_id = s.master_id) |
| -> Seq Scan on master m (cost=0.00..35.03 rows=1003 width=4) (actual time=0.013..0.178 rows=1003 loops=1) |
| -> Hash (cost=2281.42..2281.42 rows=1003 width=12) (actual time=30.065..30.066 rows=1003 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 52kB |
| -> Subquery Scan on s (cost=2261.36..2281.42 rows=1003 width=12) (actual time=29.593..29.908 rows=1003 loops=1) |
| -> HashAggregate (cost=2261.36..2271.39 rows=1003 width=12) (actual time=29.592..29.780 rows=1003 loops=1) |
| Group Key: slave.master_id |
| Batches: 1 Memory Usage: 193kB |
| -> Seq Scan on slave (cost=0.00..1810.24 rows=90224 width=4) (actual time=0.010..10.469 rows=90224 loops=1) |
| Planning Time: 0.278 ms |
| Execution Time: 30.644 ms |