Postgres 13

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?.
-- comprehensive test table
CREATE TABLE gaps (
  all_full           text NOT NULL PRIMARY KEY CHECK (all_full <> '')  -- with idx!
, all_null           text CHECK (all_null IS NULL)
, all_empty          text NOT NULL CHECK (all_empty = '')
, has_empty          text NOT NULL
, has_null           text
, has_null_n_empty   text
);

INSERT INTO gaps VALUES
  ('x', NULL, '', 'x', 'x', 'x')
, ('y', NULL, '', '', NULL, NULL)
, ('z', NULL, '', '', NULL, '');

CREATE TABLE

INSERT 0 3

-- function
CREATE FUNCTION f_has_missing(_tbl regclass, _col text, OUT has_missing boolean)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE
   format($$SELECT EXISTS (SELECT FROM %s WHERE %2$I = '' OR %2$I IS NULL)$$, _tbl, _col)
   INTO has_missing;
END
$func$;

CREATE FUNCTION

-- test
SELECT f_has_missing('gaps', 'all_full')
     , f_has_missing('gaps', 'all_null')
     , f_has_missing('gaps', 'all_empty')
     , f_has_missing('gaps', 'has_empty')
     , f_has_missing('gaps', 'has_null')
     , f_has_missing('gaps', 'has_null_n_empty');

f_has_missing
f_has_missing
f_has_missing
f_has_missing
f_has_missing
f_has_missing

f
t
t
t
t
t

SELECT 1

-- disable seq scan to show applicable index
set enable_seqscan = off;

SET

-- can't use index :(
EXPLAIN SELECT EXISTS (SELECT FROM gaps WHERE (all_full <> '') IS NOT TRUE);

QUERY PLAN

Result  (cost=24.62..24.63 rows=1 width=1)

  InitPlan 1 (returns $0)

    ->  Bitmap Heap Scan on gaps  (cost=10.00..24.62 rows=1 width=0)

          Filter: ((all_full <> ''::text) IS NOT TRUE)

          ->  Bitmap Index Scan on gaps_pkey  (cost=0.00..10.00 rows=370 width=0)

EXPLAIN

-- can't use index :(
EXPLAIN SELECT EXISTS (SELECT FROM gaps WHERE coalesce(all_full, '') = '');

QUERY PLAN

Result  (cost=17.31..17.32 rows=1 width=1)

  InitPlan 1 (returns $0)

    ->  Bitmap Heap Scan on gaps  (cost=10.00..24.62 rows=2 width=0)

          Filter: (COALESCE(all_full, ''::text) = ''::text)

          ->  Bitmap Index Scan on gaps_pkey  (cost=0.00..10.00 rows=370 width=0)

EXPLAIN

-- can use index :)
EXPLAIN SELECT EXISTS (SELECT FROM gaps WHERE all_full = '' OR all_full IS NULL);

QUERY PLAN

Result  (cost=10.69..10.70 rows=1 width=1)

  InitPlan 1 (returns $0)

    ->  Bitmap Heap Scan on gaps  (cost=8.32..15.43 rows=3 width=0)

          Recheck Cond: ((all_full = ''::text) OR (all_full IS NULL))

          ->  BitmapOr  (cost=8.32..8.32 rows=3 width=0)

                ->  Bitmap Index Scan on gaps_pkey  (cost=0.00..4.16 rows=1 width=0)

                      Index Cond: (all_full = ''::text)

                ->  Bitmap Index Scan on gaps_pkey  (cost=0.00..4.16 rows=2 width=0)

                      Index Cond: (all_full IS NULL)

EXPLAIN