Calculate and show total Disk Space for partitioned tables in PostgreSQL table list

[yoni-yad2]

Is your feature request related to a problem? Please describe. For now the "Row Count Estimate" column shows "-1" for partitioned tables in the table list in the PostgreSQL schema properties window.

Describe the solution you'd like Calculate the sum of row count estimates of all partitions of a table and show it in the table list.

Describe alternatives you've considered Using this query:

 SELECT 
  parent.relname AS parent_table,
    sum(child.reltuples) as total_row_count
FROM
  pg_namespace parent_ns
JOIN 
  pg_class parent ON parent.relnamespace = parent_ns.oid
JOIN 
  pg_inherits i ON parent.oid = i.inhparent
JOIN 
  pg_class child ON i.inhrelid = child.oid
JOIN 
  pg_namespace child_ns ON child.relnamespace = child_ns.oid
WHERE 
  parent_ns.nspname = 'myschema'
  and parent.relkind!='I'
  group by parent.relname;

[LonwoLonwo]

Hello @yoni-yad2

Ok, we need to show the row count correctly for partitioned tables.

But I'm not sure about your query. Does it return the correct result starting with big row numbers or something? Because it shows me null value for table with 29 and 1029 rows.

[LonwoLonwo]

Ok, maybe we will find another solution without analyzing first. Thanks for the report.

[yoni-yad2]

I changed the title of this issue because ANALYZE [PARTITIONED TABLE] is a good enough workaround for generating total row count estimate. Total "Disk Space" is still 0 though.

Here's a query for generating it:

 SELECT 
  parent.relname AS parent_table,
  parent.reltuples AS row_count_estimate,
  pg_size_pretty(sum(pg_catalog.pg_total_relation_size(child.oid))) as total_disk_space
FROM
  pg_namespace parent_ns
JOIN 
  pg_class parent ON parent.relnamespace = parent_ns.oid
JOIN 
  pg_inherits i ON parent.oid = i.inhparent
JOIN 
  pg_class child ON i.inhrelid = child.oid
JOIN 
  pg_namespace child_ns ON child.relnamespace = child_ns.oid
WHERE 
  parent_ns.nspname = 'myschema'
    AND parent.relkind in ('r','p')
  group by parent.relname,parent.reltuples;

[hciq-justin-michalczak]

The proposed SQL query by @yoni-yad2 only works for tables with a single layer of partitions. Tables with partitions of partitions are still reported with zero size, and there's additional rows for non-leaf partitions.

This stack-overflow has a functional recursive version:

https://stackoverflow.com/a/59266949

WITH RECURSIVE tables AS (
  SELECT
    c.oid AS parent,
    c.oid AS relid,
    1     AS level
  FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_inherits AS i ON c.oid = i.inhrelid
    -- p = partitioned table, r = normal table
  WHERE c.relkind IN ('p', 'r')
    -- not having a parent table -> we only get the partition heads
    AND i.inhrelid IS NULL

  UNION ALL

  SELECT
    p.parent         AS parent,
    c.oid            AS relid,
    p.level + 1      AS level
  FROM tables AS p
  LEFT JOIN pg_catalog.pg_inherits AS i ON p.relid = i.inhparent
  LEFT JOIN pg_catalog.pg_class AS c ON c.oid = i.inhrelid AND c.relispartition
  WHERE c.oid IS NOT NULL
)
SELECT
  parent ::REGCLASS                                  AS table_name,
  array_agg(relid :: REGCLASS)                       AS all_partitions,
  pg_size_pretty(sum(pg_total_relation_size(relid))) AS pretty_total_size,
  sum(pg_total_relation_size(relid))                 AS total_size
FROM tables
GROUP BY parent
ORDER BY sum(pg_total_relation_size(relid)) DESC

Limiting this to singular tables can be done by filtering on the first SELECT via comparing the oid to a qualified name:

AND c.oid = 'schema.table'::REGCLASS