Postgres - approximate number of rows in partitioned tables

Simple way, if the child partitions follow a naming scheme:

SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
where relname like 'my_table_%'
ORDER BY relname DESC ;

Found a way to do this:

            SELECT
                SUM(child.reltuples)    AS number_of_records_all_partitions
            FROM pg_inherits
                JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
                JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
                JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
                JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
            WHERE parent.relname = 'my_table_name';

I am also using the system ANALYZE command, and the parent/child relationship between a table and it's partitions.

If you want to handle multiple levels of inheritance correctly (c inherits b inherits a) then you need a recursive query:

WITH RECURSIVE tree AS
(
  SELECT i.inhrelid AS oid
  FROM pg_inherits i
    JOIN pg_class base_t ON i.inhparent = base_t.oid
    JOIN pg_namespace base_ns ON base_t.relnamespace = base_ns.oid
  WHERE base_ns.nspname = 'base_schema' 
    AND base_t.relname = 'base_table_name'

  UNION ALL

  SELECT i.inhrelid AS oid
  FROM pg_inherits i
    JOIN tree b ON i.inhparent = b.oid
    JOIN pg_class cl on cl.oid = i.inhrelid
)
SELECT sum(tbl.reltuples)
FROM tree tr
  JOIN pg_class tbl ON tr.oid = tbl.oid;

This one worked for me for a hash partitioned table:

SELECT sum(reltuples)::int8
FROM pg_catalog.pg_class
WHERE relispartition = true
    AND reltype != 0 -- ignore indexes

I'd like @a_horse_with_no_name answer and plus it, but recursive query may be much simpler:

WITH RECURSIVE tree AS (
  SELECT i.inhrelid AS oid
  FROM pg_inherits i
  WHERE i.inhparent = 'scheme.my_table_name'::regclass
UNION ALL
  SELECT i.inhrelid AS oid
  FROM pg_inherits i
    JOIN tree b ON i.inhparent = b.oid
)
SELECT sum(tbl.reltuples)
FROM tree tr
  JOIN pg_class tbl ON tr.oid = tbl.oid;

That may be also transformed into a more generic variant for all tables, ready for join (including unpartitioned):

    WITH RECURSIVE tables_tree AS ( -- See https://stackoverflow.com/questions/30592826/postgres-approximate-number-of-rows-in-partitioned-tables/68958004#68958004
      SELECT oid AS oid, oid as parent_oid
      FROM pg_class i
    UNION ALL
      SELECT i.inhrelid AS oid, t.parent_oid
      FROM pg_inherits i
        JOIN tables_tree t ON i.inhparent = t.oid
    ), tables_total_size AS (
        SELECT sum(tbl.reltuples) as estimated_table_rows_sum, t.parent_oid
        FROM tables_tree t
          JOIN pg_class tbl ON t.oid = tbl.oid
        GROUP BY t.parent_oid
    )
    SELECT *, parent_oid::regclass as table_name
    FROM tables_total_size

DB-fiddle