Postgres 10

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