add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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