Postgres 13

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

--Test 1 counting ~ 1 % of all rows

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT COUNT(NULLIF(amount > 148, FALSE))            FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 3.353 ms

Execution Time: 376.497 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT SUM((amount > 148)::int)                      FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.151 ms

Execution Time: 231.049 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT SUM(CASE WHEN amount > 148 THEN 1 ELSE 0 END) FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.066 ms

Execution Time: 246.551 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT COUNT(CASE WHEN amount > 148 THEN 1 END)      FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.069 ms

Execution Time: 372.707 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT COUNT((amount > 148) OR NULL)                 FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.068 ms

Execution Time: 337.522 ms

EXPLAIN

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

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.126 ms

Execution Time: 150.035 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM tbl WHERE amount > 148;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=3390 loops=3)

                    Filter: (amount > 148)

                    Rows Removed by Filter: 329943

Planning Time: 0.201 ms

Execution Time: 178.624 ms

EXPLAIN

SET max_parallel_workers_per_gather = 0;  -- disable parallel query

SET

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

QUERY PLAN

Aggregate (actual rows=1 loops=1)

  ->  Seq Scan on tbl (actual rows=1000000 loops=1)

Planning Time: 0.052 ms

Execution Time: 199.508 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM tbl WHERE amount > 148;

QUERY PLAN

Aggregate (actual rows=1 loops=1)

  ->  Seq Scan on tbl (actual rows=10170 loops=1)

        Filter: (amount > 148)

        Rows Removed by Filter: 989830

Planning Time: 0.068 ms

Execution Time: 196.160 ms

EXPLAIN

RESET max_parallel_workers_per_gather;

RESET

-- Test 2 counting ~ 33 % of all rows

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT COUNT(NULLIF(amount > 148, FALSE))            FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.065 ms

Execution Time: 287.138 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT SUM((amount > 100)::int)                      FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.065 ms

Execution Time: 272.176 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.070 ms

Execution Time: 277.798 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT COUNT(CASE WHEN amount > 100 THEN 1 END)      FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.066 ms

Execution Time: 275.744 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT COUNT((amount > 100) OR NULL)                 FROM tbl;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.067 ms

Execution Time: 221.253 ms

EXPLAIN

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

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=333333 loops=3)

Planning Time: 0.063 ms

Execution Time: 192.739 ms

EXPLAIN

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

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Seq Scan on tbl (actual rows=110151 loops=3)

                    Filter: (amount > 100)

                    Rows Removed by Filter: 223182

Planning Time: 0.092 ms

Execution Time: 164.520 ms

EXPLAIN

SET max_parallel_workers_per_gather = 0;  -- disable parallel query

SET

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

QUERY PLAN

Aggregate (actual rows=1 loops=1)

  ->  Seq Scan on tbl (actual rows=1000000 loops=1)

Planning Time: 0.052 ms

Execution Time: 148.874 ms

EXPLAIN

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

QUERY PLAN

Aggregate (actual rows=1 loops=1)

  ->  Seq Scan on tbl (actual rows=330454 loops=1)

        Filter: (amount > 100)

        Rows Removed by Filter: 669546

Planning Time: 0.064 ms

Execution Time: 113.316 ms

EXPLAIN

RESET max_parallel_workers_per_gather;

RESET

-- index for the final test
CREATE INDEX ON tbl (amount);

CREATE INDEX

-- with index
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM tbl WHERE amount > 148;

QUERY PLAN

Aggregate (actual rows=1 loops=1)

  ->  Index Only Scan using tbl_amount_idx on tbl (actual rows=10170 loops=1)

        Index Cond: (amount > 148)

        Heap Fetches: 0

Planning Time: 0.414 ms

Execution Time: 1.294 ms

EXPLAIN

-- with index
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM tbl WHERE amount > 100;

QUERY PLAN

Finalize Aggregate (actual rows=1 loops=1)

  ->  Gather (actual rows=3 loops=1)

        Workers Planned: 2

        Workers Launched: 2

        ->  Partial Aggregate (actual rows=1 loops=3)

              ->  Parallel Index Only Scan using tbl_amount_idx on tbl (actual rows=110151 loops=3)

                    Index Cond: (amount > 100)

                    Heap Fetches: 0

Planning Time: 0.062 ms

Execution Time: 117.589 ms

EXPLAIN

SET max_parallel_workers_per_gather = 0;  -- disable parallel query

SET

-- with index
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT count(*) FROM tbl WHERE amount > 100;

QUERY PLAN

Aggregate (actual rows=1 loops=1)

  ->  Index Only Scan using tbl_amount_idx on tbl (actual rows=330454 loops=1)

        Index Cond: (amount > 100)

        Heap Fetches: 0

Planning Time: 0.084 ms

Execution Time: 30.651 ms

EXPLAIN