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. 709925 fiddles created (14147 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)


analyze benchmark_booleans;
 hidden batch(es)


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


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


explain (analyze,verbose,costs,buffers) select count(*) from benchmark_booleans where is_deleted = true;
QUERY PLAN
Aggregate (cost=1615.96..1615.97 rows=1 width=8) (actual time=14.267..14.267 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=398 read=93 written=21
-> Seq Scan on fiddle_kkpaefzaxetrnadnhdqz.benchmark_booleans (cost=0.00..1491.00 rows=49983 width=0) (actual time=0.046..11.187 rows=50193 loops=1)
Output: id, is_deleted, deleted_at
Filter: benchmark_booleans.is_deleted
Rows Removed by Filter: 49807
Buffers: shared hit=398 read=93 written=21
Planning Time: 0.081 ms
Execution Time: 14.290 ms
 hidden batch(es)


explain (analyze,verbose,costs,buffers) select count(*) from benchmark_booleans where deleted_at is null;
QUERY PLAN
Aggregate (cost=1615.78..1615.79 rows=1 width=8) (actual time=13.857..13.857 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=430 read=61 written=11
-> Seq Scan on fiddle_kkpaefzaxetrnadnhdqz.benchmark_booleans (cost=0.00..1491.00 rows=49910 width=0) (actual time=0.022..10.748 rows=50201 loops=1)
Output: id, is_deleted, deleted_at
Filter: (benchmark_booleans.deleted_at IS NULL)
Rows Removed by Filter: 49799
Buffers: shared hit=430 read=61 written=11
Planning Time: 0.078 ms
Execution Time: 13.882 ms
 hidden batch(es)


select * from pg_stats where tablename='benchmark_booleans' and attname='deleted_at';
schemaname tablename attname inherited null_frac avg_width n_distinct most_common_vals most_common_freqs histogram_bounds correlation most_common_elems most_common_elem_freqs elem_count_histogram
fiddle_kkpaefzaxetrnadnhdqz benchmark_booleans deleted_at f 0.4991 8 7084 {"2002-04-25 20:12:12.026655","2018-02-04 20:12:12.026655","2002-04-28 20:12:12.026655","2009-05-20 20:12:12.026655","2010-12-02 20:12:12.026655","2020-04-15 20:12:12.026655"} {0.0003,0.0003,0.00026666667,0.00026666667,0.00026666667,0.00026666667} {"2001-01-17 20:12:12.026655","2001-03-25 20:12:12.026655","2001-06-10 20:12:12.026655","2001-08-29 20:12:12.026655","2001-11-10 20:12:12.026655","2002-01-23 20:12:12.026655","2002-03-30 20:12:12.026655","2002-06-15 20:12:12.026655","2002-08-30 20:12:12.026655","2002-11-16 20:12:12.026655","2003-01-18 20:12:12.026655","2003-04-05 20:12:12.026655","2003-06-22 20:12:12.026655","2003-08-28 20:12:12.026655","2003-11-04 20:12:12.026655","2004-01-20 20:12:12.026655","2004-04-09 20:12:12.026655","2004-07-04 20:12:12.026655","2004-09-15 20:12:12.026655","2004-11-26 20:12:12.026655","2005-02-04 20:12:12.026655","2005-04-21 20:12:12.026655","2005-07-04 20:12:12.026655","2005-09-16 20:12:12.026655","2005-11-30 20:12:12.026655","2006-02-17 20:12:12.026655","2006-05-11 20:12:12.026655","2006-07-18 20:12:12.026655","2006-09-30 20:12:12.026655","2006-12-03 20:12:12.026655","2007-02-08 20:12:12.026655","2007-04-24 20:12:12.026655","2007-07-10 20:12:12.026655","2007-09-25 20:12:12.026655","2007-12-05 20:12:12.026655","2008-02-25 20:12:12.026655","2008-05-14 20:12:12.026655","2008-07-18 20:12:12.026655","2008-09-22 20:12:12.026655","2008-12-05 20:12:12.026655","2009-02-25 20:12:12.026655","2009-05-05 20:12:12.026655","2009-07-17 20:12:12.026655","2009-09-20 20:12:12.026655","2009-11-23 20:12:12.026655","2010-01-24 20:12:12.026655","2010-04-10 20:12:12.026655","2010-06-19 20:12:12.026655","2010-08-30 20:12:12.026655","2010-11-16 20:12:12.026655","2011-01-21 20:12:12.026655","2011-03-27 20:12:12.026655","2011-06-07 20:12:12.026655","2011-08-23 20:12:12.026655","2011-10-27 20:12:12.026655","2012-01-03 20:12:12.026655","2012-03-25 20:12:12.026655","2012-06-10 20:12:12.026655","2012-08-19 20:12:12.026655","2012-10-27 20:12:12.026655","2013-01-09 20:12:12.026655","2013-03-29 20:12:12.026655","2013-06-09 20:12:12.026655","2013-08-18 20:12:12.026655","2013-10-29 20:12:12.026655","2014-01-06 20:12:12.026655","2014-03-15 20:12:12.026655","2014-05-28 20:12:12.026655","2014-08-06 20:12:12.026655","2014-10-17 20:12:12.026655","2014-12-25 20:12:12.026655","2015-03-10 20:12:12.026655","2015-05-19 20:12:12.026655","2015-07-29 20:12:12.026655","2015-10-02 20:12:12.026655","2015-12-10 20:12:12.026655","2016-02-21 20:12:12.026655","2016-05-09 20:12:12.026655","2016-07-09 20:12:12.026655","2016-09-30 20:12:12.026655","2016-12-11 20:12:12.026655","2017-03-04 20:12:12.026655","2017-05-18 20:12:12.026655","2017-08-06 20:12:12.026655","2017-10-27 20:12:12.026655","2018-01-11 20:12:12.026655","2018-04-03 20:12:12.026655","2018-06-15 20:12:12.026655","2018-08-31 20:12:12.026655","2018-11-09 20:12:12.026655","2019-01-16 20:12:12.026655","2019-03-16 20:12:12.026655","2019-06-09 20:12:12.026655","2019-08-17 20:12:12.026655","2019-11-06 20:12:12.026655","2020-01-19 20:12:12.026655","2020-03-27 20:12:12.026655","2020-06-06 20:12:12.026655","2020-08-16 20:12:12.026655","2020-11-02 20:12:12.026655","2021-01-15 20:12:12.026655"} -0.0058487207
 hidden batch(es)