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 tbl (
tbl_id int
, amount int NOT NULL
);
INSERT INTO tbl
SELECT g, (random() * 150)::int
FROM generate_series (1, 1000000) g;
CREATE TABLE
INSERT 0 1000000
VACUUM ANALYZE tbl;
VACUUM
SELECT COUNT(*) FROM tbl WHERE amount > 148;
| count |
|---|
| 10243 |
SELECT 1
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(NULLIF(amount > 148, FALSE)) FROM tbl;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=12716.88..12716.89 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=12716.67..12716.88 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=11716.67..11716.68 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..8591.67 rows=416667 width=4) (actual rows=333333 loops=3) |
| Planning time: 0.069 ms |
| Execution time: 151.468 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT SUM((amount > 148)::int) FROM tbl;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=12716.88..12716.89 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=12716.67..12716.88 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=11716.67..11716.68 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..8591.67 rows=416667 width=4) (actual rows=333333 loops=3) |
| Planning time: 0.110 ms |
| Execution time: 156.695 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT SUM(CASE WHEN amount > 148 THEN 1 ELSE 0 END) FROM tbl;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=11675.22..11675.23 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=11675.00..11675.21 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=10675.00..10675.01 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..8591.67 rows=416667 width=4) (actual rows=333333 loops=3) |
| Planning time: 0.063 ms |
| Execution time: 211.680 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(CASE WHEN amount > 148 THEN 1 END) FROM tbl;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=11675.22..11675.23 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=11675.00..11675.21 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=10675.00..10675.01 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..8591.67 rows=416667 width=4) (actual rows=333333 loops=3) |
| Planning time: 0.064 ms |
| Execution time: 163.236 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT((amount > 148) OR NULL) FROM tbl;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=11675.22..11675.23 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=11675.00..11675.21 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=10675.00..10675.01 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..8591.67 rows=416667 width=4) (actual rows=333333 loops=3) |
| Planning time: 0.062 ms |
| Execution time: 152.275 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FILTER (WHERE amount > 148) FROM tbl;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=11675.22..11675.23 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=11675.00..11675.21 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=10675.00..10675.01 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..8591.67 rows=416667 width=4) (actual rows=333333 loops=3) |
| Planning time: 0.062 ms |
| Execution time: 138.809 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 148;
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=10649.17..10649.18 rows=1 width=8) (actual rows=1 loops=1) |
| -> Gather (cost=10648.96..10649.17 rows=2 width=8) (actual rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=9648.96..9648.97 rows=1 width=8) (actual rows=1 loops=3) |
| -> Parallel Seq Scan on tbl (cost=0.00..9633.33 rows=6250 width=0) (actual rows=3414 loops=3) |
| Filter: (amount > 148) |
| Rows Removed by Filter: 329919 |
| Planning time: 0.073 ms |
| Execution time: 95.935 ms |
EXPLAIN
CREATE INDEX ON tbl (amount);
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 148;
CREATE INDEX
| QUERY PLAN |
|---|
| Aggregate (cost=468.43..468.44 rows=1 width=8) (actual rows=1 loops=1) |
| -> Index Only Scan using tbl_amount_idx on tbl (cost=0.42..430.93 rows=15000 width=0) (actual rows=10243 loops=1) |
| Index Cond: (amount > 148) |
| Heap Fetches: 0 |
| Planning time: 0.558 ms |
| Execution time: 1.745 ms |
EXPLAIN
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 148; -- repeat
| QUERY PLAN |
|---|
| Aggregate (cost=468.43..468.44 rows=1 width=8) (actual rows=1 loops=1) |
| -> Index Only Scan using tbl_amount_idx on tbl (cost=0.42..430.93 rows=15000 width=0) (actual rows=10243 loops=1) |
| Index Cond: (amount > 148) |
| Heap Fetches: 0 |
| Planning time: 0.196 ms |
| Execution time: 1.344 ms |
EXPLAIN