Postgres - approximate number of rows in partitioned tables

Question

I am looking for a way to approximate the number of rows in a partitioned table. I want to avoid using count(*) because of the size of the data. I tried using this:

SELECT reltuples FROM pg_class WHERE relname = 'my_table_name';

but it always returns zero. I am assuming it's because that table is partitioned.

Is there a way to use pg_class/pg_inherits to get the number of rows in all partitions?

Any ideas are much appreciated!

[Here](http://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres) how to count rows in your db — The Reason, Jun 02 '15 at 10:41
You'll have to find all direct and indirect inheritance (recursive CTE) then sum the stats estimates for each. — Craig Ringer, Jun 02 '15 at 10:56
Thanks! I found a way to use inheritance, posted it in an answer. — Sanuye, Jun 02 '15 at 11:05

score 10 · Answer 1 · answered Jun 29 '16 at 16:31

10

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 ;

answered Jun 29 '16 at 16:31

lucrussell

5,032
2
33
39

score 4 · Answer 2 · answered Jun 02 '15 at 11:04

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.

This is pretty good! I'd love to make this a sub-select or a join of some sort that works even if the table isn't inherited. — Volte, Apr 08 '17 at 01:57

score 3 · Answer 3 · answered Jun 02 '15 at 13:39

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;

Can that be the case in partitioning though? – Sanuye Jun 03 '15 at 08:36 — Sanuye, Jun 03 '15 at 08:36

score 0 · Answer 4 · answered May 25 '21 at 19:59

0

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

answered May 25 '21 at 19:59

camdenl

1,159
4
21
32

Hubbitus · Answer 5 · 2021-08-30T14:48:50.330

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

Postgres - approximate number of rows in partitioned tables

5 Answers5