By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE
| setseed |
|---|
SELECT 1
INSERT 0 400000
| a | b | c | d | e | f |
|---|---|---|---|---|---|
| 771 | 566 | 373 | 551 | 292 | 417 |
| 917 | 743 | 229 | 678 | 138 | 412 |
| 124 | 856 | 750 | 407 | 831 | 192 |
| 802 | 1 | 485 | 723 | 346 | 58 |
| 311 | 503 | 847 | 464 | 440 | 161 |
| 127 | 21 | 220 | 118 | 577 | 834 |
| 949 | 480 | 455 | 857 | 932 | 436 |
| 564 | 375 | 994 | 866 | 574 | 877 |
| 112 | 143 | 40 | 318 | 429 | 75 |
| 988 | 8 | 39 | 177 | 549 | 638 |
SELECT 10
CREATE INDEX
VACUUM
| QUERY PLAN |
|---|
| Bitmap Heap Scan on table_a (cost=7.49..1064.88 rows=396 width=24) (actual time=0.125..0.511 rows=396 loops=1) |
| Recheck Cond: (a = 5) |
| Heap Blocks: exact=365 |
| -> Bitmap Index Scan on idx_a (cost=0.00..7.39 rows=396 width=0) (actual time=0.072..0.072 rows=396 loops=1) |
| Index Cond: (a = 5) |
| Planning Time: 0.233 ms |
| Execution Time: 0.566 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Bitmap Heap Scan on table_a (cost=46.93..2759.35 rows=3936 width=24) (actual time=0.554..2.741 rows=3903 loops=1) |
| Recheck Cond: (a < 10) |
| Heap Blocks: exact=2020 |
| -> Bitmap Index Scan on idx_a (cost=0.00..45.94 rows=3936 width=0) (actual time=0.297..0.297 rows=3903 loops=1) |
| Index Cond: (a < 10) |
| Planning Time: 0.041 ms |
| Execution Time: 2.883 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Index Only Scan using idx_a on table_a (cost=0.42..11.35 rows=396 width=4) (actual time=0.009..0.043 rows=396 loops=1) |
| Index Cond: (a = 5) |
| Heap Fetches: 0 |
| Planning Time: 0.035 ms |
| Execution Time: 0.074 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Index Only Scan using idx_a on table_a (cost=0.42..85.30 rows=3936 width=4) (actual time=0.007..0.342 rows=3903 loops=1) |
| Index Cond: (a < 10) |
| Heap Fetches: 0 |
| Planning Time: 0.028 ms |
| Execution Time: 0.476 ms |
EXPLAIN
CREATE INDEX
VACUUM
| QUERY PLAN |
|---|
| Index Only Scan using idx_a2 on table_a (cost=0.42..15.34 rows=395 width=24) (actual time=0.043..0.108 rows=396 loops=1) |
| Index Cond: (a = 5) |
| Heap Fetches: 0 |
| Planning Time: 0.128 ms |
| Execution Time: 0.137 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Index Only Scan using idx_a2 on table_a (cost=0.42..133.13 rows=3469 width=24) (actual time=0.010..0.626 rows=3903 loops=1) |
| Index Cond: (a < 10) |
| Heap Fetches: 0 |
| Planning Time: 0.064 ms |
| Execution Time: 0.763 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Index Only Scan using idx_a on table_a (cost=0.42..11.34 rows=395 width=4) (actual time=0.009..0.044 rows=396 loops=1) |
| Index Cond: (a = 5) |
| Heap Fetches: 0 |
| Planning Time: 0.035 ms |
| Execution Time: 0.065 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Index Only Scan using idx_a on table_a (cost=0.42..77.13 rows=3469 width=4) (actual time=0.007..0.333 rows=3903 loops=1) |
| Index Cond: (a < 10) |
| Heap Fetches: 0 |
| Planning Time: 0.043 ms |
| Execution Time: 0.466 ms |
EXPLAIN