How to find the tables that are taking up the pg_toast data in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Reasons for using this article:

• Find the size of pg_toast tables.
• Order the pg_toast tables to see which one consumes the most. 
• Find the associated user tables to these toast tables.

Environment

Product Version: 6.12

Resolution

Run the following query in the database where the toast tables exist. 

SELECT oid::regclass AS Table,
       reltoastrelid::regclass As TOAST_Table,
       pg_relation_size(reltoastrelid) AS toast_size
FROM pg_class
WHERE relkind = 'r'
  AND reltoastrelid <> 0
ORDER BY 3 DESC;