Re: BUG #17942: vacuumdb doesn't populate extended statistics on partitioned tables

At Fri, 26 May 2023 16:49:35 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> present in the list. Anyway, I don't find a simple way to do that for
> now.

For 12 or later, pg_partition_ancestors() is available. Thus something
like the following query would work.

+WITH inh_children AS
+ (SELECT tc.relid::name, t.nspname FROM
+  (SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
+   JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid
+   LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid
+   WHERE c.relkind OPERATOR(pg_catalog.=) 'p') t,
+  LATERAL
+   (SELECT tt.relid, t.nspname
+    FROM pg_catalog.pg_partition_tree(t.relname::text) tt WHERE isleaf) tc)

SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
 JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid 
 LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid 
 WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm', 'p'])
+  AND NOT EXISTS (SELECT * FROM inh_children i WHERE i.relid = c.relname AND i.nspname = ns.nspname)

ORDER BY c.relpages DESC;

The lines prefixed by a '+' removes other than the topmost-parents of trees.

For 11, we need to do the same withouth using the function.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center