Обсуждение: Toast table infi
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
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;
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@gmail.com> wrote:
HiI 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.ThanksSathish Reddy
On Sunday, May 26, 2024, Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:
HiI am trying to get toast tables information
You may wish to narrow that down.
from cluster level all databases
Not directly doable - you will need to execute one query per database.
but not working.please help me on queries to sort out these.
You should at least share what you did try.
David J.
The convention on these lists is to inline-post (and trim) or bottom-post.
On Sunday, May 26, 2024, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
On Sunday, May 26, 2024, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
Please try out the following query.This will display all the tables with associated Toast tables in the cluster.
pg_class does not produce cluster-wide results (nor do the others).
David J.
On Mon, 2024-05-27 at 11:20 +0530, Sathish Reddy wrote: > I am trying to get toast tables information What kind of information are you interested in? Yours, Laurenz Albe
I am looking for all toast tables with size from cluster level all databases.it need to perform as like pg_stat_activity
Thanks
Sathish Reddy
On Mon, May 27, 2024, 2:35 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2024-05-27 at 11:20 +0530, Sathish Reddy wrote:
> I am trying to get toast tables information
What kind of information are you interested in?
Yours,
Laurenz Albe
On Mon, 2024-05-27 at 15:16 +0530, Sathish Reddy wrote: > I am looking for all toast tables with size from cluster level all databases. > it need to perform as like pg_stat_activity SELECT oid::regclass, pg_relation_size(oid) FROM pg_class WHERE relkind = 't' AND relnamespace = 'pg_toast'::regnamespace; That will show you all TOAST tables from the current database. You have to connect to all databases and run the query in each one. Yours, Laurenz Albe