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 > 100;

count

330203

SELECT 1

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(NULLIF(amount > 100, 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.071 ms

Execution time: 156.379 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT SUM((amount > 100)::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.078 ms

Execution time: 155.349 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT SUM(CASE WHEN amount > 100 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.062 ms

Execution time: 169.544 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(CASE WHEN amount > 100 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.065 ms

Execution time: 181.828 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT((amount > 100) 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.064 ms

Execution time: 176.880 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FILTER (WHERE amount > 100)          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: 171.754 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100;

QUERY PLAN

Finalize Aggregate  (cost=10987.72..10987.73 rows=1 width=8) (actual rows=1 loops=1)

  ->  Gather  (cost=10987.50..10987.71 rows=2 width=8) (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate  (cost=9987.50..9987.51 rows=1 width=8) (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl  (cost=0.00..9633.33 rows=141667 width=0) (actual rows=110068 loops=3)

                    Filter: (amount > 100)

                    Rows Removed by Filter: 223266

Planning time: 0.073 ms

Execution time: 129.503 ms

EXPLAIN

CREATE INDEX ON tbl (amount);
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100;

CREATE INDEX

QUERY PLAN

Finalize Aggregate  (cost=8657.71..8657.72 rows=1 width=8) (actual rows=1 loops=1)

  ->  Gather  (cost=8657.39..8657.70 rows=3 width=8) (actual rows=4 loops=1)

        Workers Planned: 3

        Workers Launched: 3

        ->  Partial Aggregate  (cost=7657.39..7657.40 rows=1 width=8) (actual rows=1 loops=4)

              ->  Parallel Index Only Scan using tbl_amount_idx on tbl  (cost=0.42..7383.20 rows=109677 width=0) (actual rows=82551 loops=4)

                    Index Cond: (amount > 100)

                    Heap Fetches: 0

Planning time: 0.760 ms

Execution time: 108.742 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100; -- repeat

QUERY PLAN

Finalize Aggregate  (cost=8657.71..8657.72 rows=1 width=8) (actual rows=1 loops=1)

  ->  Gather  (cost=8657.39..8657.70 rows=3 width=8) (actual rows=4 loops=1)

        Workers Planned: 3

        Workers Launched: 3

        ->  Partial Aggregate  (cost=7657.39..7657.40 rows=1 width=8) (actual rows=1 loops=4)

              ->  Parallel Index Only Scan using tbl_amount_idx on tbl  (cost=0.42..7383.20 rows=109677 width=0) (actual rows=82551 loops=4)

                    Index Cond: (amount > 100)

                    Heap Fetches: 0

Planning time: 0.333 ms

Execution time: 80.668 ms

EXPLAIN