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. 921136 fiddles created (11951 in the last week).

CREATE TABLE benchmark_booleans ( id SERIAL UNIQUE NOT NULL, is_deleted boolean, -- not unique deleted_at timestamp ); CREATE INDEX idx_is_deleted on benchmark_booleans (is_deleted); CREATE INDEX idx_deleted_at on benchmark_booleans (deleted_at); INSERT INTO benchmark_booleans (is_deleted, deleted_at) SELECT (RANDOM() > 0.5) as is_deleted, case when RANDOM() < 0.5 then null else now() - trunc(random() * 20) * '1 year'::interval + trunc(random() * 365) * '1 day'::interval end as deleted_at FROM generate_series(1,100000);
100000 rows affected
 hidden batch(es)


select count(*) from benchmark_booleans where is_deleted = true;
count
49921
 hidden batch(es)


select count(*) from benchmark_booleans where deleted_at is null;
count
49940
 hidden batch(es)


explain (analyze,verbose,costs,buffers) select count(*) from benchmark_booleans where is_deleted = true;
QUERY PLAN
Aggregate (cost=1590.23..1590.24 rows=1 width=8) (actual time=27.429..27.429 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=456 read=35 written=32
-> Seq Scan on fiddle_xdjlwcliluiuwevjsrvm.benchmark_booleans (cost=0.00..1468.09 rows=48854 width=0) (actual time=0.239..20.531 rows=49921 loops=1)
Output: id, is_deleted, deleted_at
Filter: benchmark_booleans.is_deleted
Rows Removed by Filter: 50079
Buffers: shared hit=456 read=35 written=32
Planning Time: 0.101 ms
Execution Time: 27.456 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) select count(*) from benchmark_booleans where deleted_at is null;
QUERY PLAN
Aggregate (cost=525.62..525.63 rows=1 width=8) (actual time=220.955..220.956 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=36 read=584 written=244
-> Bitmap Heap Scan on fiddle_xdjlwcliluiuwevjsrvm.benchmark_booleans (cost=12.08..524.40 rows=489 width=0) (actual time=6.625..215.882 rows=49940 loops=1)
Recheck Cond: (benchmark_booleans.deleted_at IS NULL)
Heap Blocks: exact=491
Buffers: shared hit=36 read=584 written=244
-> Bitmap Index Scan on idx_deleted_at (cost=0.00..11.96 rows=489 width=0) (actual time=6.556..6.556 rows=49940 loops=1)
Index Cond: (benchmark_booleans.deleted_at IS NULL)
Buffers: shared hit=5 read=124 written=65
Planning Time: 0.618 ms
Execution Time: 220.995 ms
 hidden batch(es)