clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582679 fiddles created (13278 in the last week).

CREATE TABLE tbl ( tbl_id int , amount int NOT NULL ); INSERT INTO tbl SELECT g, (random() * 150)::int FROM generate_series (1, 1000000) g;
1000000 rows affected
 hidden batch(es)


VACUUM ANALYZE tbl;
 hidden batch(es)


SELECT COUNT(*) FROM tbl WHERE amount > 100;
count
329935
 hidden batch(es)


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.038 ms
Execution time: 141.086 ms
 hidden batch(es)


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.058 ms
Execution time: 135.274 ms
 hidden batch(es)


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.048 ms
Execution time: 139.353 ms
 hidden batch(es)


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.049 ms
Execution time: 144.387 ms
 hidden batch(es)


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.048 ms
Execution time: 135.023 ms
 hidden batch(es)


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.046 ms
Execution time: 131.186 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100;
QUERY PLAN
Finalize Aggregate (cost=10982.51..10982.52 rows=1 width=8) (actual rows=1 loops=1)
-> Gather (cost=10982.29..10982.50 rows=2 width=8) (actual rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=9982.29..9982.30 rows=1 width=8) (actual rows=1 loops=3)
-> Parallel Seq Scan on tbl (cost=0.00..9633.33 rows=139583 width=0) (actual rows=109978 loops=3)
Filter: (amount > 100)
Rows Removed by Filter: 223355
Planning time: 0.055 ms
Execution time: 101.094 ms
 hidden batch(es)


CREATE INDEX ON tbl (amount); EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100;
QUERY PLAN
Finalize Aggregate (cost=8544.05..8544.06 rows=1 width=8) (actual rows=1 loops=1)
-> Gather (cost=8543.73..8544.04 rows=3 width=8) (actual rows=4 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Partial Aggregate (cost=7543.73..7543.74 rows=1 width=8) (actual rows=1 loops=4)
-> Parallel Index Only Scan using tbl_amount_idx on tbl (cost=0.42..7273.57 rows=108065 width=0) (actual rows=82484 loops=4)
Index Cond: (amount > 100)
Heap Fetches: 0
Planning time: 0.178 ms
Execution time: 58.434 ms
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100; -- repeat
QUERY PLAN
Finalize Aggregate (cost=8544.05..8544.06 rows=1 width=8) (actual rows=1 loops=1)
-> Gather (cost=8543.73..8544.04 rows=3 width=8) (actual rows=4 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Partial Aggregate (cost=7543.73..7543.74 rows=1 width=8) (actual rows=1 loops=4)
-> Parallel Index Only Scan using tbl_amount_idx on tbl (cost=0.42..7273.57 rows=108065 width=0) (actual rows=82484 loops=4)
Index Cond: (amount > 100)
Heap Fetches: 0
Planning time: 0.148 ms
Execution time: 56.243 ms
 hidden batch(es)