Postgres 16

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

SELECT 10

do $f$
declare column_name_ text;
        dynamic_sql_query text;
        dynamic_sql_query_per_table text:=$$SELECT COUNT(DISTINCT "userId") AS %1$I
                                                 ,'%2$I' as "attrName"
                                                 ,now() AS "dateWhenSnapshotInserted"
                                           FROM orig_table 
                                           WHERE %2$I IS NOT NULL$$;
begin
for column_name_ in select column_name 
                    from information_schema.columns
                    where table_name='orig_table'
loop
    dynamic_sql_query:=concat_ws(' UNION ALL '
                                 ,dynamic_sql_query
                                 ,format( dynamic_sql_query_per_table
                                         ,column_name_||'Count'
                                         ,column_name_));
end loop;
execute format('CREATE MATERIALIZED VIEW orig_table_counts AS %s',dynamic_sql_query);
end $f$;

select * from orig_table_counts;

DO

userIdCount	attrName	dateWhenSnapshotInserted
10	"userId"	2024-04-16 09:22:17.106873+00
7	attr1	2024-04-16 09:22:17.106873+00
6	attr2	2024-04-16 09:22:17.106873+00
7	attr3	2024-04-16 09:22:17.106873+00

SELECT 4