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?.
SELECT 1000000
| QUERY PLAN |
|---|
| Gather (cost=13298.80..25597.91 rows=4 width=4) (actual time=344.824..345.431 rows=0 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=491 read=10462 dirtied=5278 written=5182 |
| -> Parallel Hash Join (cost=12298.80..24597.51 rows=2 width=4) (actual time=265.272..265.275 rows=0 loops=3) |
| Hash Cond: (r1.e = r2.e) |
| Buffers: shared hit=491 read=10462 dirtied=5278 written=5182 |
| -> Parallel Seq Scan on tst_eav r1 (cost=0.00..12298.65 rows=12 width=4) (actual time=6.653..120.700 rows=32 loops=3) |
| Filter: ((a = 1) AND (v = 1)) |
| Rows Removed by Filter: 333301 |
| Buffers: shared hit=224 read=5182 |
| -> Parallel Hash (cost=12298.65..12298.65 rows=12 width=4) (actual time=133.672..133.673 rows=39 loops=3) |
| Buckets: 1024 Batches: 1 Memory Usage: 104kB |
| Buffers: shared hit=128 read=5278 dirtied=5278 written=5182 |
| -> Parallel Seq Scan on tst_eav r2 (cost=0.00..12298.65 rows=12 width=4) (actual time=12.221..133.323 rows=39 loops=3) |
| Filter: ((a = 2) AND (v = 2)) |
| Rows Removed by Filter: 333295 |
| Buffers: shared hit=128 read=5278 dirtied=5278 written=5182 |
| Planning Time: 1.543 ms |
| Execution Time: 345.489 ms |
EXPLAIN
| QUERY PLAN |
|---|
| HashSetOp Intersect (cost=1000.00..26603.88 rows=26 width=8) (actual time=281.154..281.258 rows=0 loops=1) |
| Buffers: shared hit=736 read=10076 |
| -> Append (cost=1000.00..26603.74 rows=56 width=8) (actual time=1.079..281.143 rows=213 loops=1) |
| Buffers: shared hit=736 read=10076 |
| -> Subquery Scan on "*SELECT* 1" (cost=1000.00..13301.73 rows=28 width=8) (actual time=1.078..141.610 rows=97 loops=1) |
| Buffers: shared hit=320 read=5086 |
| -> Gather (cost=1000.00..13301.45 rows=28 width=4) (actual time=1.078..141.584 rows=97 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=320 read=5086 |
| -> Parallel Seq Scan on tst_eav (cost=0.00..12298.65 rows=12 width=4) (actual time=0.665..120.419 rows=32 loops=3) |
| Filter: ((a = 1) AND (v = 1)) |
| Rows Removed by Filter: 333301 |
| Buffers: shared hit=320 read=5086 |
| -> Subquery Scan on "*SELECT* 2" (cost=1000.00..13301.73 rows=28 width=8) (actual time=12.091..139.498 rows=116 loops=1) |
| Buffers: shared hit=416 read=4990 |
| -> Gather (cost=1000.00..13301.45 rows=28 width=4) (actual time=12.090..139.469 rows=116 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=416 read=4990 |
| -> Parallel Seq Scan on tst_eav tst_eav_1 (cost=0.00..12298.65 rows=12 width=4) (actual time=16.218..108.795 rows=39 loops=3) |
| Filter: ((a = 2) AND (v = 2)) |
| Rows Removed by Filter: 333295 |
| Buffers: shared hit=416 read=4990 |
| Planning Time: 0.374 ms |
| Execution Time: 281.308 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Finalize GroupAggregate (cost=15596.74..15603.29 rows=1 width=4) (actual time=169.480..169.657 rows=1 loops=1) |
| Group Key: e |
| Filter: (count(*) = 2) |
| Rows Removed by Filter: 211 |
| Buffers: shared hit=603 read=4901 written=9 |
| -> Gather Merge (cost=15596.74..15602.46 rows=46 width=12) (actual time=169.447..169.596 rows=212 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=603 read=4901 written=9 |
| -> Partial GroupAggregate (cost=14596.72..14597.12 rows=23 width=12) (actual time=146.575..146.607 rows=71 loops=3) |
| Group Key: e |
| Buffers: shared hit=603 read=4901 written=9 |
| -> Sort (cost=14596.72..14596.78 rows=23 width=4) (actual time=146.566..146.574 rows=71 loops=3) |
| Sort Key: e |
| Sort Method: quicksort Memory: 28kB |
| Worker 0: Sort Method: quicksort Memory: 29kB |
| Worker 1: Sort Method: quicksort Memory: 27kB |
| Buffers: shared hit=603 read=4901 written=9 |
| -> Parallel Seq Scan on tst_eav (cost=0.00..14596.20 rows=23 width=4) (actual time=3.332..146.369 rows=71 loops=3) |
| Filter: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Rows Removed by Filter: 333262 |
| Buffers: shared hit=505 read=4901 written=9 |
| Planning Time: 0.711 ms |
| Execution Time: 169.795 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=15603.29..15604.54 rows=1 width=4) (actual time=161.718..161.783 rows=0 loops=1) |
| Group Key: e |
| Filter: (array_length(array_agg(DISTINCT t.*), 1) = 2) |
| Rows Removed by Filter: 212 |
| Buffers: shared hit=597 read=4817 written=2 |
| -> Sort (cost=15603.29..15603.43 rows=55 width=40) (actual time=161.066..161.150 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 41kB |
| Buffers: shared hit=590 read=4816 written=2 |
| -> Gather (cost=1000.00..15601.70 rows=55 width=40) (actual time=1.427..160.983 rows=213 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| Buffers: shared hit=590 read=4816 written=2 |
| -> Parallel Seq Scan on tst_eav t (cost=0.00..14596.20 rows=23 width=40) (actual time=0.812..137.559 rows=71 loops=3) |
| Filter: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Rows Removed by Filter: 333262 |
| Buffers: shared hit=590 read=4816 written=2 |
| Planning Time: 0.127 ms |
| Execution Time: 161.872 ms |
EXPLAIN
CREATE INDEX
| QUERY PLAN |
|---|
| Hash Join (cost=104.95..200.38 rows=3 width=4) (actual time=3.044..3.046 rows=0 loops=1) |
| Hash Cond: (r1.e = r2.e) |
| Buffers: shared hit=32 read=186 |
| -> Bitmap Heap Scan on tst_eav r1 (cost=4.68..99.96 rows=25 width=4) (actual time=0.128..1.191 rows=97 loops=1) |
| Recheck Cond: ((a = 1) AND (v = 1)) |
| Heap Blocks: exact=97 |
| Buffers: shared hit=12 read=88 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.091..0.091 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared read=3 |
| -> Hash (cost=99.96..99.96 rows=25 width=4) (actual time=1.782..1.783 rows=116 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 13kB |
| Buffers: shared hit=20 read=98 |
| -> Bitmap Heap Scan on tst_eav r2 (cost=4.68..99.96 rows=25 width=4) (actual time=0.053..1.676 rows=116 loops=1) |
| Recheck Cond: ((a = 2) AND (v = 2)) |
| Heap Blocks: exact=115 |
| Buffers: shared hit=20 read=98 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.027..0.027 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=2 read=1 |
| Planning Time: 0.542 ms |
| Execution Time: 3.152 ms |
EXPLAIN
| QUERY PLAN |
|---|
| HashSetOp Intersect (cost=4.68..200.79 rows=24 width=8) (actual time=0.402..0.403 rows=0 loops=1) |
| Buffers: shared hit=218 |
| -> Append (cost=4.68..200.66 rows=50 width=8) (actual time=0.074..0.368 rows=213 loops=1) |
| Buffers: shared hit=218 |
| -> Subquery Scan on "*SELECT* 1" (cost=4.68..100.21 rows=25 width=8) (actual time=0.073..0.191 rows=97 loops=1) |
| Buffers: shared hit=100 |
| -> Bitmap Heap Scan on tst_eav (cost=4.68..99.96 rows=25 width=4) (actual time=0.071..0.177 rows=97 loops=1) |
| Recheck Cond: ((a = 1) AND (v = 1)) |
| Heap Blocks: exact=97 |
| Buffers: shared hit=100 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.050..0.050 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Subquery Scan on "*SELECT* 2" (cost=4.68..100.21 rows=25 width=8) (actual time=0.026..0.157 rows=116 loops=1) |
| Buffers: shared hit=118 |
| -> Bitmap Heap Scan on tst_eav tst_eav_1 (cost=4.68..99.96 rows=25 width=4) (actual time=0.025..0.145 rows=116 loops=1) |
| Recheck Cond: ((a = 2) AND (v = 2)) |
| Heap Blocks: exact=115 |
| Buffers: shared hit=118 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.014..0.014 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.143 ms |
| Execution Time: 0.458 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=197.36..198.29 rows=1 width=4) (actual time=0.295..0.351 rows=1 loops=1) |
| Group Key: e |
| Filter: (count(*) = 2) |
| Rows Removed by Filter: 211 |
| Buffers: shared hit=215 |
| -> Sort (cost=197.36..197.49 rows=50 width=4) (actual time=0.280..0.291 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 34kB |
| Buffers: shared hit=215 |
| -> Bitmap Heap Scan on tst_eav (cost=9.38..195.95 rows=50 width=4) (actual time=0.102..0.232 rows=213 loops=1) |
| Recheck Cond: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Heap Blocks: exact=209 |
| Buffers: shared hit=215 |
| -> BitmapOr (cost=9.38..9.38 rows=50 width=0) (actual time=0.083..0.083 rows=0 loops=1) |
| Buffers: shared hit=6 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.071..0.071 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.011..0.011 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.106 ms |
| Execution Time: 0.387 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=197.36..198.51 rows=1 width=4) (actual time=0.544..0.545 rows=0 loops=1) |
| Group Key: e |
| Filter: (array_length(array_agg(DISTINCT t.*), 1) = 2) |
| Rows Removed by Filter: 212 |
| Buffers: shared hit=215 |
| -> Sort (cost=197.36..197.49 rows=50 width=40) (actual time=0.265..0.278 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 41kB |
| Buffers: shared hit=215 |
| -> Bitmap Heap Scan on tst_eav t (cost=9.38..195.95 rows=50 width=40) (actual time=0.046..0.217 rows=213 loops=1) |
| Recheck Cond: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Heap Blocks: exact=209 |
| Buffers: shared hit=215 |
| -> BitmapOr (cost=9.38..9.38 rows=50 width=0) (actual time=0.023..0.024 rows=0 loops=1) |
| Buffers: shared hit=6 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.012..0.012 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Bitmap Index Scan on eav_idx1 (cost=0.00..4.67 rows=25 width=0) (actual time=0.010..0.010 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.060 ms |
| Execution Time: 0.598 ms |
EXPLAIN
DROP INDEX
CREATE INDEX
| QUERY PLAN |
|---|
| Hash Join (cost=104.95..200.38 rows=3 width=4) (actual time=0.388..0.390 rows=0 loops=1) |
| Hash Cond: (r1.e = r2.e) |
| Buffers: shared hit=214 read=4 |
| -> Bitmap Heap Scan on tst_eav r1 (cost=4.68..99.96 rows=25 width=4) (actual time=0.049..0.143 rows=97 loops=1) |
| Recheck Cond: ((a = 1) AND (v = 1)) |
| Heap Blocks: exact=97 |
| Buffers: shared hit=97 read=3 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.034..0.034 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared read=3 |
| -> Hash (cost=99.96..99.96 rows=25 width=4) (actual time=0.230..0.230 rows=116 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 13kB |
| Buffers: shared hit=117 read=1 |
| -> Bitmap Heap Scan on tst_eav r2 (cost=4.68..99.96 rows=25 width=4) (actual time=0.081..0.210 rows=116 loops=1) |
| Recheck Cond: ((a = 2) AND (v = 2)) |
| Heap Blocks: exact=115 |
| Buffers: shared hit=117 read=1 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.020..0.020 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=2 read=1 |
| Planning Time: 0.259 ms |
| Execution Time: 0.432 ms |
EXPLAIN
| QUERY PLAN |
|---|
| HashSetOp Intersect (cost=4.68..200.79 rows=24 width=8) (actual time=0.317..0.318 rows=0 loops=1) |
| Buffers: shared hit=218 |
| -> Append (cost=4.68..200.66 rows=50 width=8) (actual time=0.029..0.283 rows=213 loops=1) |
| Buffers: shared hit=218 |
| -> Subquery Scan on "*SELECT* 1" (cost=4.68..100.21 rows=25 width=8) (actual time=0.028..0.155 rows=97 loops=1) |
| Buffers: shared hit=100 |
| -> Bitmap Heap Scan on tst_eav (cost=4.68..99.96 rows=25 width=4) (actual time=0.026..0.142 rows=97 loops=1) |
| Recheck Cond: ((a = 1) AND (v = 1)) |
| Heap Blocks: exact=97 |
| Buffers: shared hit=100 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.014..0.014 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Subquery Scan on "*SELECT* 2" (cost=4.68..100.21 rows=25 width=8) (actual time=0.023..0.108 rows=116 loops=1) |
| Buffers: shared hit=118 |
| -> Bitmap Heap Scan on tst_eav tst_eav_1 (cost=4.68..99.96 rows=25 width=4) (actual time=0.023..0.096 rows=116 loops=1) |
| Recheck Cond: ((a = 2) AND (v = 2)) |
| Heap Blocks: exact=115 |
| Buffers: shared hit=118 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.012..0.012 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.093 ms |
| Execution Time: 0.380 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=197.36..198.29 rows=1 width=4) (actual time=0.273..0.328 rows=1 loops=1) |
| Group Key: e |
| Filter: (count(*) = 2) |
| Rows Removed by Filter: 211 |
| Buffers: shared hit=215 |
| -> Sort (cost=197.36..197.49 rows=50 width=4) (actual time=0.257..0.268 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 34kB |
| Buffers: shared hit=215 |
| -> Bitmap Heap Scan on tst_eav (cost=9.38..195.95 rows=50 width=4) (actual time=0.045..0.210 rows=213 loops=1) |
| Recheck Cond: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Heap Blocks: exact=209 |
| Buffers: shared hit=215 |
| -> BitmapOr (cost=9.38..9.38 rows=50 width=0) (actual time=0.024..0.025 rows=0 loops=1) |
| Buffers: shared hit=6 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.013..0.013 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.011..0.011 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.075 ms |
| Execution Time: 0.363 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=197.36..198.51 rows=1 width=4) (actual time=0.581..0.582 rows=0 loops=1) |
| Group Key: e |
| Filter: (array_length(array_agg(DISTINCT t.*), 1) = 2) |
| Rows Removed by Filter: 212 |
| Buffers: shared hit=215 |
| -> Sort (cost=197.36..197.49 rows=50 width=40) (actual time=0.300..0.312 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 41kB |
| Buffers: shared hit=215 |
| -> Bitmap Heap Scan on tst_eav t (cost=9.38..195.95 rows=50 width=40) (actual time=0.046..0.252 rows=213 loops=1) |
| Recheck Cond: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Heap Blocks: exact=209 |
| Buffers: shared hit=215 |
| -> BitmapOr (cost=9.38..9.38 rows=50 width=0) (actual time=0.023..0.024 rows=0 loops=1) |
| Buffers: shared hit=6 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.012..0.012 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Bitmap Index Scan on eav_idx2 (cost=0.00..4.67 rows=25 width=0) (actual time=0.010..0.011 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.064 ms |
| Execution Time: 0.615 ms |
EXPLAIN
DROP INDEX
CREATE INDEX
| QUERY PLAN |
|---|
| Hash Join (cost=104.95..200.38 rows=3 width=4) (actual time=0.305..0.307 rows=0 loops=1) |
| Hash Cond: (r1.e = r2.e) |
| Buffers: shared hit=214 read=4 |
| -> Bitmap Heap Scan on tst_eav r1 (cost=4.68..99.96 rows=25 width=4) (actual time=0.045..0.130 rows=97 loops=1) |
| Recheck Cond: ((a = 1) AND (v = 1)) |
| Heap Blocks: exact=97 |
| Buffers: shared hit=97 read=3 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.031..0.031 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared read=3 |
| -> Hash (cost=99.96..99.96 rows=25 width=4) (actual time=0.162..0.162 rows=116 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 13kB |
| Buffers: shared hit=117 read=1 |
| -> Bitmap Heap Scan on tst_eav r2 (cost=4.68..99.96 rows=25 width=4) (actual time=0.030..0.145 rows=116 loops=1) |
| Recheck Cond: ((a = 2) AND (v = 2)) |
| Heap Blocks: exact=115 |
| Buffers: shared hit=117 read=1 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.017..0.017 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=2 read=1 |
| Planning Time: 0.247 ms |
| Execution Time: 0.345 ms |
EXPLAIN
| QUERY PLAN |
|---|
| HashSetOp Intersect (cost=4.68..200.79 rows=24 width=8) (actual time=0.231..0.232 rows=0 loops=1) |
| Buffers: shared hit=218 |
| -> Append (cost=4.68..200.66 rows=50 width=8) (actual time=0.024..0.201 rows=213 loops=1) |
| Buffers: shared hit=218 |
| -> Subquery Scan on "*SELECT* 1" (cost=4.68..100.21 rows=25 width=8) (actual time=0.024..0.090 rows=97 loops=1) |
| Buffers: shared hit=100 |
| -> Bitmap Heap Scan on tst_eav (cost=4.68..99.96 rows=25 width=4) (actual time=0.022..0.078 rows=97 loops=1) |
| Recheck Cond: ((a = 1) AND (v = 1)) |
| Heap Blocks: exact=97 |
| Buffers: shared hit=100 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.012..0.012 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Subquery Scan on "*SELECT* 2" (cost=4.68..100.21 rows=25 width=8) (actual time=0.019..0.093 rows=116 loops=1) |
| Buffers: shared hit=118 |
| -> Bitmap Heap Scan on tst_eav tst_eav_1 (cost=4.68..99.96 rows=25 width=4) (actual time=0.019..0.082 rows=116 loops=1) |
| Recheck Cond: ((a = 2) AND (v = 2)) |
| Heap Blocks: exact=115 |
| Buffers: shared hit=118 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.010..0.010 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.081 ms |
| Execution Time: 0.264 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=197.36..198.29 rows=1 width=4) (actual time=0.210..0.260 rows=1 loops=1) |
| Group Key: e |
| Filter: (count(*) = 2) |
| Rows Removed by Filter: 211 |
| Buffers: shared hit=215 |
| -> Sort (cost=197.36..197.49 rows=50 width=4) (actual time=0.196..0.207 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 34kB |
| Buffers: shared hit=215 |
| -> Bitmap Heap Scan on tst_eav (cost=9.38..195.95 rows=50 width=4) (actual time=0.040..0.154 rows=213 loops=1) |
| Recheck Cond: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Heap Blocks: exact=209 |
| Buffers: shared hit=215 |
| -> BitmapOr (cost=9.38..9.38 rows=50 width=0) (actual time=0.022..0.022 rows=0 loops=1) |
| Buffers: shared hit=6 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.011..0.011 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.010..0.010 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.061 ms |
| Execution Time: 0.291 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=197.36..198.51 rows=1 width=4) (actual time=0.504..0.504 rows=0 loops=1) |
| Group Key: e |
| Filter: (array_length(array_agg(DISTINCT t.*), 1) = 2) |
| Rows Removed by Filter: 212 |
| Buffers: shared hit=215 |
| -> Sort (cost=197.36..197.49 rows=50 width=40) (actual time=0.252..0.263 rows=213 loops=1) |
| Sort Key: e |
| Sort Method: quicksort Memory: 41kB |
| Buffers: shared hit=215 |
| -> Bitmap Heap Scan on tst_eav t (cost=9.38..195.95 rows=50 width=40) (actual time=0.053..0.213 rows=213 loops=1) |
| Recheck Cond: (((a = 1) AND (v = 1)) OR ((a = 2) AND (v = 2))) |
| Heap Blocks: exact=209 |
| Buffers: shared hit=215 |
| -> BitmapOr (cost=9.38..9.38 rows=50 width=0) (actual time=0.032..0.032 rows=0 loops=1) |
| Buffers: shared hit=6 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.022..0.022 rows=97 loops=1) |
| Index Cond: ((a = 1) AND (v = 1)) |
| Buffers: shared hit=3 |
| -> Bitmap Index Scan on eav_idx3 (cost=0.00..4.67 rows=25 width=0) (actual time=0.010..0.010 rows=116 loops=1) |
| Index Cond: ((a = 2) AND (v = 2)) |
| Buffers: shared hit=3 |
| Planning Time: 0.064 ms |
| Execution Time: 0.532 ms |
EXPLAIN