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?.

CREATE TABLE fortune500(
  id serial PRIMARY KEY
, ticker int
, profits_change text
, industry text
);

INSERT INTO fortune500 (ticker, profits_change, industry)
VALUES
  (5, 'a' , NULL)
, (6, 'a' , NULL)
, (6, NULL, NULL);

CREATE TABLE



INSERT 0 3


CREATE OR REPLACE FUNCTION f_count_nulls(_tbl regclass)
  RETURNS TABLE (column_name text, missing_values bigint)
  LANGUAGE plpgsql STABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT format(
   $$
   SELECT x.*
   FROM  (SELECT count(*) AS ct, %s FROM %s) t
   CROSS  JOIN LATERAL (VALUES %s) x(col, nulls)
   ORDER  BY nulls DESC, col DESC
   $$, string_agg(format('count(%1$I) AS %1$I', attname), ', ')
     , $1
     , string_agg(format('(%1$L, ct - %1$I)', attname), ', ')
      )
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = $1
   AND    attnum > 0
   AND    NOT attisdropped
   -- more filters?
   );
END
$func$;

CREATE FUNCTION


SELECT * FROM f_count_nulls('fortune500');


column_name
missing_values


industry
3

profits_change
1

ticker
0

id
0



SELECT 4