Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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

analyze benchmark_booleans;
select count(*) from benchmark_booleans where is_deleted = true;


count


50193


select count(*) from benchmark_booleans where deleted_at is null;


count


50201


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


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


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
null
null
null