clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
≡
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. 1048185 fiddles created (11079 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
330264
…
 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.144 ms
Execution time: 162.670 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.089 ms
Execution time: 171.083 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.094 ms
Execution time: 168.808 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.063 ms
Execution time: 162.169 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.063 ms
Execution time: 157.936 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.060 ms
Execution time: 157.274 ms
…
 hidden batch(es)
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100;
QUERY PLAN
Finalize Aggregate (cost=10977.30..10977.31 rows=1 width=8) (actual rows=1 loops=1)
-> Gather (cost=10977.08..10977.29 rows=2 width=8) (actual rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=9977.08..9977.09 rows=1 width=8) (actual rows=1 loops=3)
-> Parallel Seq Scan on tbl (cost=0.00..9633.33 rows=137500 width=0) (actual rows=110088 loops=3)
Filter: (amount > 100)
Rows Removed by Filter: 223245
Planning time: 0.091 ms
Execution time: 128.738 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=8430.39..8430.40 rows=1 width=8) (actual rows=1 loops=1)
-> Gather (cost=8430.07..8430.38 rows=3 width=8) (actual rows=4 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Partial Aggregate (cost=7430.07..7430.08 rows=1 width=8) (actual rows=1 loops=4)
-> Parallel Index Only Scan using tbl_amount_idx on tbl (cost=0.42..7163.94 rows=106452 width=0) (actual rows=82566 loops=4)
Index Cond: (amount > 100)
Heap Fetches: 0
Planning time: 0.774 ms
Execution time: 82.306 ms
…
 hidden batch(es)
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM tbl WHERE amount > 100; -- repeat
QUERY PLAN
Finalize Aggregate (cost=8430.39..8430.40 rows=1 width=8) (actual rows=1 loops=1)
-> Gather (cost=8430.07..8430.38 rows=3 width=8) (actual rows=4 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Partial Aggregate (cost=7430.07..7430.08 rows=1 width=8) (actual rows=1 loops=4)
-> Parallel Index Only Scan using tbl_amount_idx on tbl (cost=0.42..7163.94 rows=106452 width=0) (actual rows=82566 loops=4)
Index Cond: (amount > 100)
Heap Fetches: 0
Planning time: 0.155 ms
Execution time: 62.405 ms
…
 hidden batch(es)