Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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: 0.806 ms

Execution Time: 159.514 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.094 ms

Execution Time: 133.926 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.063 ms

Execution Time: 131.945 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.097 ms

Execution Time: 129.948 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.062 ms

Execution Time: 132.768 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.124 ms

Execution Time: 122.537 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=3406 loops=3)

                    Filter: (amount > 148)

                    Rows Removed by Filter: 329927

Planning Time: 0.772 ms

Execution Time: 105.647 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.051 ms

Execution Time: 109.391 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=10219 loops=1)

        Filter: (amount > 148)

        Rows Removed by Filter: 989781

Planning Time: 0.073 ms

Execution Time: 86.988 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.061 ms

Execution Time: 128.759 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.062 ms

Execution Time: 132.780 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.097 ms

Execution Time: 135.530 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.097 ms

Execution Time: 133.709 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.063 ms

Execution Time: 138.614 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: 132.477 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=110311 loops=3)

                    Filter: (amount > 100)

                    Rows Removed by Filter: 223022

Planning Time: 0.080 ms

Execution Time: 116.192 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.051 ms

Execution Time: 117.821 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=330933 loops=1)

        Filter: (amount > 100)

        Rows Removed by Filter: 669067

Planning Time: 0.066 ms

Execution Time: 104.043 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=10219 loops=1)

        Index Cond: (amount > 148)

        Heap Fetches: 0

Planning Time: 0.902 ms

Execution Time: 1.043 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=110311 loops=3)

                    Index Cond: (amount > 100)

                    Heap Fetches: 0

Planning Time: 0.056 ms

Execution Time: 42.536 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=330933 loops=1)

        Index Cond: (amount > 100)

        Heap Fetches: 0

Planning Time: 0.079 ms

Execution Time: 27.706 ms

EXPLAIN