Db2 Developer-C 11.1
Firebird 3.0
MariaDB 10.2 (unavailable)
MariaDB 10.3
MariaDB 10.4
MariaDB 10.5
MySQL 5.5
MySQL 5.6
MySQL 5.7
MySQL 8.0
Oracle 11g Release 2
Oracle 18c
Postgres 8.4 (unavailable)
Postgres 9.4 (unavailable)
Postgres 9.5
Postgres 9.6
Postgres 10
Postgres 11
Postgres 12
Postgres 13
SQLite 3.8 (unavailable)
SQLite 3.16 (unavailable)
SQLite 3.27
SQL Server 2012 (unavailable)
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2017 (Linux)
SQL Server 2019
SQL Server 2019 (Linux)
No sample DB
run
clear
markdown
compare
help
best fiddles
feedback
dbanow.uk
a leap of faith?
Fiddle Statistics
MySQL 8.0
SQLite 3.27
Oracle 18c
Firebird 3.0
MariaDB 10.4
MySQL 5.7
MySQL 5.6
MySQL 5.5
MariaDB 10.3
Db2 Developer-C 11.1
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2017 (Linux)
SQL Server 2019 (Linux)
SQL Server 2019
Postgres 12
Postgres 9.5
Postgres 11
Postgres 10
Postgres 9.6
MariaDB 10.5
Oracle 11g Release 2
Postgres 13
≡
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
.
1451362 fiddles created (18693 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)