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 table test (
id integer,
pid integer,
name varchar(2),
val integer
);
create index ix_test__pid on test(pid);
create index ix_test__name_val on test(name,val);
insert into test
(id, pid, name, val)
values
('1', '1', 'aa', '10'),
('2', '1', 'bb', '20'),
('3', '1', 'cc', '30'),
('4', '2', 'aa', '10'),
('5', '2', 'bb', '20'),
('6', '2', 'cc', '30'),
('7', '3', 'aa', '10'),
('8', '3', 'bb', '20'),
('9', '3', 'cc', '999');
9 rows affected
explain (analyze,buffers)
select distinct pid from test
where
pid in (select pid from test where (name,val) = ('aa',10))
and pid in (select pid from test where (name,val) = ('bb',20))
and pid in (select pid from test where (name,val) = ('cc',30));
| 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 |
-- works on all RDBMS
explain (analyze,buffers)
select pid from test where (name,val) = ('aa',10)
and pid in (
select pid from test where (name,val) = ('bb',20)
and pid in (
select pid from test where (name,val) = ('cc',30)
)
);
| 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 |
-- works on most RDBMS, MySQL has no INTERSECT
explain (analyze,buffers)
select pid from test where (name,val) = ('aa',10)
intersect
select pid from test where (name,val) = ('bb',20)
intersect
select pid from test where (name,val) = ('cc',30);
| 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 |
-- works on all RDBMS
explain (analyze,buffers)
select a.pid
from test a, test b, test c
where (a.name,a.val) = ('aa',10)
and (b.name,b.val) = ('bb',20)
and (c.name,c.val) = ('cc',30)
and (a.pid = b.pid and b.pid = c.pid);
| 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 |
-- same as above. for JOIN purists
explain (analyze,buffers)
select a.pid
from test a
cross join test b
cross join test c
where (a.name,a.val) = ('aa',10)
and (b.name,b.val) = ('bb',20)
and (c.name,c.val) = ('cc',30)
and (a.pid = b.pid and b.pid = c.pid);
| 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 |
-- just count
explain (analyze,buffers)
select t.pid
from test t
where (t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') )
group by t.pid
having count(*) = 3;
| 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 |
explain (analyze,buffers)
select t.pid
from test t
group by t.pid
having sum( ((t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') ))::int ) = 3
| 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 |