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?.
9 rows affected
QUERY PLAN |
---|
HashAggregate (cost=49.13..51.09 rows=196 width=4) (actual time=0.215..0.216 rows=2 loops=1) |
Group Key: test.pid |
Buffers: shared hit=12 |
-> Hash Semi Join (cost=28.76..48.64 rows=196 width=4) (actual time=0.173..0.208 rows=6 loops=1) |
Hash Cond: (test.pid = test_3.pid) |
Buffers: shared hit=12 |
-> Hash Semi Join (cost=20.57..37.24 rows=392 width=12) (actual time=0.107..0.138 rows=9 loops=1) |
Hash Cond: (test.pid = test_2.pid) |
Buffers: shared hit=10 |
-> Nested Loop (cost=12.39..22.64 rows=785 width=8) (actual time=0.045..0.071 rows=9 loops=1) |
Buffers: shared hit=8 |
-> HashAggregate (cost=8.17..8.18 rows=1 width=4) (actual time=0.021..0.022 rows=3 loops=1) |
Group Key: test_1.pid |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test test_1 (cost=0.15..8.17 rows=1 width=4) (actual time=0.014..0.015 rows=3 loops=1) |
Index Cond: (((name)::text = 'aa'::text) AND (val = 10)) |
Buffers: shared hit=2 |
-> Bitmap Heap Scan on test (cost=4.21..14.37 rows=8 width=4) (actual time=0.009..0.010 rows=3 loops=3) |
Recheck Cond: (pid = test_1.pid) |
Heap Blocks: exact=3 |
Buffers: shared hit=6 |
-> Bitmap Index Scan on ix_test__pid (cost=0.00..4.21 rows=8 width=0) (actual time=0.004..0.004 rows=3 loops=3) |
Index Cond: (pid = test_1.pid) |
Buffers: shared hit=3 |
-> Hash (cost=8.17..8.17 rows=1 width=4) (actual time=0.033..0.033 rows=3 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test test_2 (cost=0.15..8.17 rows=1 width=4) (actual time=0.020..0.023 rows=3 loops=1) |
Index Cond: (((name)::text = 'bb'::text) AND (val = 20)) |
Buffers: shared hit=2 |
-> Hash (cost=8.17..8.17 rows=1 width=4) (actual time=0.036..0.036 rows=2 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test test_3 (cost=0.15..8.17 rows=1 width=4) (actual time=0.023..0.026 rows=2 loops=1) |
Index Cond: (((name)::text = 'cc'::text) AND (val = 30)) |
Buffers: shared hit=2 |
Planning Time: 1.305 ms |
Execution Time: 0.494 ms |
QUERY PLAN |
---|
Nested Loop Semi Join (cost=0.46..24.54 rows=1 width=4) (actual time=0.089..0.104 rows=2 loops=1) |
Join Filter: (test.pid = test_1.pid) |
Rows Removed by Join Filter: 3 |
Buffers: shared hit=24 |
-> Index Scan using ix_test__name_val on test (cost=0.15..8.17 rows=1 width=4) (actual time=0.018..0.019 rows=3 loops=1) |
Index Cond: (((name)::text = 'aa'::text) AND (val = 10)) |
Buffers: shared hit=2 |
-> Nested Loop Semi Join (cost=0.30..16.36 rows=1 width=8) (actual time=0.024..0.026 rows=2 loops=3) |
Join Filter: (test_1.pid = test_2.pid) |
Rows Removed by Join Filter: 3 |
Buffers: shared hit=22 |
-> Index Scan using ix_test__name_val on test test_1 (cost=0.15..8.17 rows=1 width=4) (actual time=0.006..0.007 rows=3 loops=3) |
Index Cond: (((name)::text = 'bb'::text) AND (val = 20)) |
Buffers: shared hit=6 |
-> Index Scan using ix_test__name_val on test test_2 (cost=0.15..8.17 rows=1 width=4) (actual time=0.004..0.005 rows=2 loops=8) |
Index Cond: (((name)::text = 'cc'::text) AND (val = 30)) |
Buffers: shared hit=16 |
Planning Time: 0.428 ms |
Execution Time: 0.154 ms |
QUERY PLAN |
---|
HashSetOp Intersect (cost=0.15..24.59 rows=1 width=8) (actual time=0.057..0.057 rows=2 loops=1) |
Buffers: shared hit=6 |
-> Append (cost=0.15..24.58 rows=2 width=8) (actual time=0.033..0.047 rows=5 loops=1) |
Buffers: shared hit=6 |
-> Result (cost=0.15..16.39 rows=1 width=8) (actual time=0.033..0.034 rows=3 loops=1) |
Buffers: shared hit=4 |
-> HashSetOp Intersect (cost=0.15..16.38 rows=1 width=8) (actual time=0.031..0.032 rows=3 loops=1) |
Buffers: shared hit=4 |
-> Append (cost=0.15..16.38 rows=2 width=8) (actual time=0.015..0.026 rows=6 loops=1) |
Buffers: shared hit=4 |
-> Subquery Scan on "*SELECT* 1" (cost=0.15..8.18 rows=1 width=8) (actual time=0.014..0.017 rows=3 loops=1) |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test (cost=0.15..8.17 rows=1 width=4) (actual time=0.013..0.015 rows=3 loops=1) |
Index Cond: (((name)::text = 'aa'::text) AND (val = 10)) |
Buffers: shared hit=2 |
-> Subquery Scan on "*SELECT* 2" (cost=0.15..8.18 rows=1 width=8) (actual time=0.005..0.007 rows=3 loops=1) |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test test_1 (cost=0.15..8.17 rows=1 width=4) (actual time=0.005..0.006 rows=3 loops=1) |
Index Cond: (((name)::text = 'bb'::text) AND (val = 20)) |
Buffers: shared hit=2 |
-> Subquery Scan on "*SELECT* 3" (cost=0.15..8.18 rows=1 width=8) (actual time=0.009..0.010 rows=2 loops=1) |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test test_2 (cost=0.15..8.17 rows=1 width=4) (actual time=0.008..0.009 rows=2 loops=1) |
Index Cond: (((name)::text = 'cc'::text) AND (val = 30)) |
Buffers: shared hit=2 |
Planning Time: 0.270 ms |
Execution Time: 0.109 ms |
QUERY PLAN |
---|
Nested Loop (cost=0.46..24.54 rows=1 width=4) (actual time=0.037..0.048 rows=2 loops=1) |
Join Filter: (a.pid = c.pid) |
Rows Removed by Join Filter: 4 |
Buffers: shared hit=14 |
-> Nested Loop (cost=0.30..16.36 rows=1 width=8) (actual time=0.021..0.032 rows=3 loops=1) |
Join Filter: (a.pid = b.pid) |
Rows Removed by Join Filter: 6 |
Buffers: shared hit=8 |
-> Index Scan using ix_test__name_val on test a (cost=0.15..8.17 rows=1 width=4) (actual time=0.013..0.014 rows=3 loops=1) |
Index Cond: (((name)::text = 'aa'::text) AND (val = 10)) |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test b (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.004 rows=3 loops=3) |
Index Cond: (((name)::text = 'bb'::text) AND (val = 20)) |
Buffers: shared hit=6 |
-> Index Scan using ix_test__name_val on test c (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.004 rows=2 loops=3) |
Index Cond: (((name)::text = 'cc'::text) AND (val = 30)) |
Buffers: shared hit=6 |
Planning Time: 0.347 ms |
Execution Time: 0.083 ms |
QUERY PLAN |
---|
Nested Loop (cost=0.46..24.54 rows=1 width=4) (actual time=0.033..0.044 rows=2 loops=1) |
Join Filter: (a.pid = c.pid) |
Rows Removed by Join Filter: 4 |
Buffers: shared hit=14 |
-> Nested Loop (cost=0.30..16.36 rows=1 width=8) (actual time=0.019..0.030 rows=3 loops=1) |
Join Filter: (a.pid = b.pid) |
Rows Removed by Join Filter: 6 |
Buffers: shared hit=8 |
-> Index Scan using ix_test__name_val on test a (cost=0.15..8.17 rows=1 width=4) (actual time=0.012..0.013 rows=3 loops=1) |
Index Cond: (((name)::text = 'aa'::text) AND (val = 10)) |
Buffers: shared hit=2 |
-> Index Scan using ix_test__name_val on test b (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.004 rows=3 loops=3) |
Index Cond: (((name)::text = 'bb'::text) AND (val = 20)) |
Buffers: shared hit=6 |
-> Index Scan using ix_test__name_val on test c (cost=0.15..8.17 rows=1 width=4) (actual time=0.003..0.003 rows=2 loops=3) |
Index Cond: (((name)::text = 'cc'::text) AND (val = 30)) |
Buffers: shared hit=6 |
Planning Time: 0.323 ms |
Execution Time: 0.089 ms |
QUERY PLAN |
---|
GroupAggregate (cost=16.52..16.55 rows=1 width=4) (actual time=0.043..0.046 rows=2 loops=1) |
Group Key: pid |
Filter: (count(*) = 3) |
Rows Removed by Filter: 1 |
Buffers: shared hit=4 |
-> Sort (cost=16.52..16.53 rows=1 width=4) (actual time=0.036..0.038 rows=8 loops=1) |
Sort Key: pid |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=4 |
-> Bitmap Heap Scan on test t (cost=12.49..16.51 rows=1 width=4) (actual time=0.019..0.021 rows=8 loops=1) |
Recheck Cond: ((((name)::text = 'aa'::text) AND (val = 10)) OR (((name)::text = 'bb'::text) AND (val = 20)) OR (((name)::text = 'cc'::text) AND (val = 30))) |
Heap Blocks: exact=1 |
Buffers: shared hit=4 |
-> BitmapOr (cost=12.49..12.49 rows=1 width=0) (actual time=0.014..0.015 rows=0 loops=1) |
Buffers: shared hit=3 |
-> Bitmap Index Scan on ix_test__name_val (cost=0.00..4.16 rows=1 width=0) (actual time=0.008..0.008 rows=3 loops=1) |
Index Cond: (((name)::text = 'aa'::text) AND (val = 10)) |
Buffers: shared hit=1 |
-> Bitmap Index Scan on ix_test__name_val (cost=0.00..4.16 rows=1 width=0) (actual time=0.003..0.003 rows=3 loops=1) |
Index Cond: (((name)::text = 'bb'::text) AND (val = 20)) |
Buffers: shared hit=1 |
-> Bitmap Index Scan on ix_test__name_val (cost=0.00..4.16 rows=1 width=0) (actual time=0.002..0.002 rows=2 loops=1) |
Index Cond: (((name)::text = 'cc'::text) AND (val = 30)) |
Buffers: shared hit=1 |
Planning Time: 0.133 ms |
Execution Time: 0.131 ms |
QUERY PLAN |
---|
HashAggregate (cost=61.03..63.53 rows=1 width=4) (actual time=0.025..0.026 rows=2 loops=1) |
Group Key: pid |
Filter: (sum((((((name)::text = 'aa'::text) AND (val = 10)) OR (((name)::text = 'bb'::text) AND (val = 20)) OR (((name)::text = 'cc'::text) AND (val = 30))))::integer) = 3) |
Rows Removed by Filter: 1 |
Buffers: shared hit=1 |
-> Seq Scan on test t (cost=0.00..25.70 rows=1570 width=20) (actual time=0.008..0.010 rows=9 loops=1) |
Buffers: shared hit=1 |
Planning Time: 0.106 ms |
Execution Time: 0.063 ms |