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. 582716 fiddles created (13315 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 > 148;
count
10061
 hidden batch(es)


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


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


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


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


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


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


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=3354 loops=3)
Filter: (amount > 148)
Rows Removed by Filter: 329980
Planning time: 0.054 ms
Execution time: 83.187 ms
 hidden batch(es)


CREATE INDEX ON tbl (amount); EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 148;
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=10061 loops=1)
Index Cond: (amount > 148)
Heap Fetches: 0
Planning time: 0.223 ms
Execution time: 1.558 ms
 hidden batch(es)


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=10061 loops=1)
Index Cond: (amount > 148)
Heap Fetches: 0
Planning time: 0.114 ms
Execution time: 1.348 ms
 hidden batch(es)