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 2000000
ALTER TABLE
UPDATE 63140
| id | case | abs |
|---|---|---|
| 3 | 0 | 0 |
| 5 | 0 | 0 |
| 10 | 3 | 3 |
| 15 | 3 | 3 |
| 22 | 1 | 1 |
| 24 | 2 | 2 |
| 27 | 3 | 3 |
| 30 | 2 | 2 |
| 31 | 0 | 0 |
| 34 | 1 | 1 |
| 35 | 0 | 0 |
| 36 | 0 | 0 |
| 41 | 2 | 2 |
| 46 | 0 | 0 |
| 57 | 0 | 0 |
| 59 | 0 | 0 |
| 61 | 2 | 2 |
| 62 | 0 | 0 |
| 68 | 2 | 2 |
| 69 | 2 | 2 |
SELECT 20
| QUERY PLAN |
|---|
| Seq Scan on public.test (cost=0.00..32407.95 rows=1862316 width=4) (actual time=0.009..510.392 rows=2000000 loops=1) |
| Output: CASE WHEN (a < 0) THEN 0 ELSE a END |
| Planning Time: 0.021 ms |
| Execution Time: 586.300 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Seq Scan on public.test (cost=0.00..41719.53 rows=1862316 width=4) (actual time=0.013..326.097 rows=2000000 loops=1) |
| Output: ((abs(a) + a) / 2) |
| Planning Time: 0.054 ms |
| Execution Time: 395.895 ms |
EXPLAIN
| QUERY PLAN |
|---|
| Seq Scan on public.test (cost=0.00..32407.95 rows=1862316 width=4) (actual time=0.018..312.091 rows=2000000 loops=1) |
| Output: GREATEST(a, 0) |
| Planning Time: 0.050 ms |
| Execution Time: 388.290 ms |
EXPLAIN