Re: Toast table infi

[Kashif Zeeshan <kashi.zeeshan@xxxxxxxxx>]

Hi

Please try out the following query.

This will display all the tables with associated Toast tables in the cluster.

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    t.relname AS toast_table_name
FROM
    pg_class c
JOIN
    pg_namespace n ON c.relnamespace = n.oid
JOIN
    pg_attribute a ON c.oid = a.attrelid
LEFT JOIN
    pg_class t ON a.attrelid = t.reltoastrelid
WHERE
    c.relkind = 'r'  -- Regular tables
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')  -- Exclude system schemas
    AND c.reltoastrelid != 0  -- Only tables with toast tables
ORDER BY
    n.nspname, c.relname;

To check if a table has a toast table .

select t1.oid, t1.relname, t1.relkind, t2.relkind, t2.relpages, t2.reltuples
from pg_class t1
inner join pg_class t2
on t1.reltoastrelid = t2.oid
where t1.relkind = 'r'
  and t2.relkind = 't';

Regards

Kashif Zeeshan

Bitnine Global

On Mon, May 27, 2024 at 10:50 AM Sathish Reddy <sathishreddy.postgresql@xxxxxxxxx> wrote:

Hi 

  I am trying to get toast tables information from cluster level all databases tables with child tables.but not working.please help me on queries to sort out these.

Thanks 

Sathish Reddy