Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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); > > <pre> > ✓ > > ✓ > > ✓ > 100000 rows affected > </pre> <!-- --> > analyze benchmark_booleans; > > <pre> > ✓ > </pre> <!-- --> > select count(*) from benchmark_booleans where is_deleted = true; > > <pre> > | count | > | ----: | > | 50193 | > </pre> <!-- --> > select count(*) from benchmark_booleans where deleted_at is null; > > <pre> > | count | > | ----: | > | 50201 | > </pre> <!-- --> > explain (analyze,verbose,costs,buffers) > select count(*) from benchmark_booleans where is_deleted = true; > > <pre> > | 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 | > | -&gt; 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 | > </pre> <!-- --> > explain (analyze,verbose,costs,buffers) > select count(*) from benchmark_booleans where deleted_at is null; > > <pre> > | 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 | > | -&gt; 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 | > </pre> <!-- --> > select * from pg_stats where tablename='benchmark_booleans' and attname='deleted_at'; > > <pre> > 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 | {&quot;2002-04-25 20:12:12.026655&quot;,&quot;2018-02-04 20:12:12.026655&quot;,&quot;2002-04-28 20:12:12.026655&quot;,&quot;2009-05-20 20:12:12.026655&quot;,&quot;2010-12-02 20:12:12.026655&quot;,&quot;2020-04-15 20:12:12.026655&quot;} | {0.0003,0.0003,0.00026666667,0.00026666667,0.00026666667,0.00026666667} | {&quot;2001-01-17 20:12:12.026655&quot;,&quot;2001-03-25 20:12:12.026655&quot;,&quot;2001-06-10 20:12:12.026655&quot;,&quot;2001-08-29 20:12:12.026655&quot;,&quot;2001-11-10 20:12:12.026655&quot;,&quot;2002-01-23 20:12:12.026655&quot;,&quot;2002-03-30 20:12:12.026655&quot;,&quot;2002-06-15 20:12:12.026655&quot;,&quot;2002-08-30 20:12:12.026655&quot;,&quot;2002-11-16 20:12:12.026655&quot;,&quot;2003-01-18 20:12:12.026655&quot;,&quot;2003-04-05 20:12:12.026655&quot;,&quot;2003-06-22 20:12:12.026655&quot;,&quot;2003-08-28 20:12:12.026655&quot;,&quot;2003-11-04 20:12:12.026655&quot;,&quot;2004-01-20 20:12:12.026655&quot;,&quot;2004-04-09 20:12:12.026655&quot;,&quot;2004-07-04 20:12:12.026655&quot;,&quot;2004-09-15 20:12:12.026655&quot;,&quot;2004-11-26 20:12:12.026655&quot;,&quot;2005-02-04 20:12:12.026655&quot;,&quot;2005-04-21 20:12:12.026655&quot;,&quot;2005-07-04 20:12:12.026655&quot;,&quot;2005-09-16 20:12:12.026655&quot;,&quot;2005-11-30 20:12:12.026655&quot;,&quot;2006-02-17 20:12:12.026655&quot;,&quot;2006-05-11 20:12:12.026655&quot;,&quot;2006-07-18 20:12:12.026655&quot;,&quot;2006-09-30 20:12:12.026655&quot;,&quot;2006-12-03 20:12:12.026655&quot;,&quot;2007-02-08 20:12:12.026655&quot;,&quot;2007-04-24 20:12:12.026655&quot;,&quot;2007-07-10 20:12:12.026655&quot;,&quot;2007-09-25 20:12:12.026655&quot;,&quot;2007-12-05 20:12:12.026655&quot;,&quot;2008-02-25 20:12:12.026655&quot;,&quot;2008-05-14 20:12:12.026655&quot;,&quot;2008-07-18 20:12:12.026655&quot;,&quot;2008-09-22 20:12:12.026655&quot;,&quot;2008-12-05 20:12:12.026655&quot;,&quot;2009-02-25 20:12:12.026655&quot;,&quot;2009-05-05 20:12:12.026655&quot;,&quot;2009-07-17 20:12:12.026655&quot;,&quot;2009-09-20 20:12:12.026655&quot;,&quot;2009-11-23 20:12:12.026655&quot;,&quot;2010-01-24 20:12:12.026655&quot;,&quot;2010-04-10 20:12:12.026655&quot;,&quot;2010-06-19 20:12:12.026655&quot;,&quot;2010-08-30 20:12:12.026655&quot;,&quot;2010-11-16 20:12:12.026655&quot;,&quot;2011-01-21 20:12:12.026655&quot;,&quot;2011-03-27 20:12:12.026655&quot;,&quot;2011-06-07 20:12:12.026655&quot;,&quot;2011-08-23 20:12:12.026655&quot;,&quot;2011-10-27 20:12:12.026655&quot;,&quot;2012-01-03 20:12:12.026655&quot;,&quot;2012-03-25 20:12:12.026655&quot;,&quot;2012-06-10 20:12:12.026655&quot;,&quot;2012-08-19 20:12:12.026655&quot;,&quot;2012-10-27 20:12:12.026655&quot;,&quot;2013-01-09 20:12:12.026655&quot;,&quot;2013-03-29 20:12:12.026655&quot;,&quot;2013-06-09 20:12:12.026655&quot;,&quot;2013-08-18 20:12:12.026655&quot;,&quot;2013-10-29 20:12:12.026655&quot;,&quot;2014-01-06 20:12:12.026655&quot;,&quot;2014-03-15 20:12:12.026655&quot;,&quot;2014-05-28 20:12:12.026655&quot;,&quot;2014-08-06 20:12:12.026655&quot;,&quot;2014-10-17 20:12:12.026655&quot;,&quot;2014-12-25 20:12:12.026655&quot;,&quot;2015-03-10 20:12:12.026655&quot;,&quot;2015-05-19 20:12:12.026655&quot;,&quot;2015-07-29 20:12:12.026655&quot;,&quot;2015-10-02 20:12:12.026655&quot;,&quot;2015-12-10 20:12:12.026655&quot;,&quot;2016-02-21 20:12:12.026655&quot;,&quot;2016-05-09 20:12:12.026655&quot;,&quot;2016-07-09 20:12:12.026655&quot;,&quot;2016-09-30 20:12:12.026655&quot;,&quot;2016-12-11 20:12:12.026655&quot;,&quot;2017-03-04 20:12:12.026655&quot;,&quot;2017-05-18 20:12:12.026655&quot;,&quot;2017-08-06 20:12:12.026655&quot;,&quot;2017-10-27 20:12:12.026655&quot;,&quot;2018-01-11 20:12:12.026655&quot;,&quot;2018-04-03 20:12:12.026655&quot;,&quot;2018-06-15 20:12:12.026655&quot;,&quot;2018-08-31 20:12:12.026655&quot;,&quot;2018-11-09 20:12:12.026655&quot;,&quot;2019-01-16 20:12:12.026655&quot;,&quot;2019-03-16 20:12:12.026655&quot;,&quot;2019-06-09 20:12:12.026655&quot;,&quot;2019-08-17 20:12:12.026655&quot;,&quot;2019-11-06 20:12:12.026655&quot;,&quot;2020-01-19 20:12:12.026655&quot;,&quot;2020-03-27 20:12:12.026655&quot;,&quot;2020-06-06 20:12:12.026655&quot;,&quot;2020-08-16 20:12:12.026655&quot;,&quot;2020-11-02 20:12:12.026655&quot;,&quot;2021-01-15 20:12:12.026655&quot;} | -0.0058487207 | <em>null</em> | <em>null</em> | <em>null</em> > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=37849052cbf703e83ad53063a3db6c57)*
back to fiddle