Query to fetch table sizes in the Analytics application
Requirement
To determine the sizes of the tables available under our analytics application, we can execute the following query in the DB Query tool and retrieve the details.
Query
Navigate to Application Settings --> Troubleshoot --> DB Query Tool --> Execute the below query
SELECT db.id as "Workspace Id", db.name as "Workspace Name", db.status as "Workspace status", obj.id as "Table Obj Id",tb.id as "Table id", obj.displayname as "Table Display name",td.table_name as "Internal Table Name", obj.status as "Table status",obj.type as "Type", iammail.email_id as "User", round(CAST(float8 (((sum(pg_table_size('"' || ist.table_schema || '"."' || ist.table_name || '"'))) / 1024) / 1024) AS numeric), 2) AS "Size in MB", pgut.n_live_tup as "Row Count" FROM zdbdatabase db JOIN zdbdbobject obj ON db.id = obj.dbid JOIN zdbtable tb ON obj.id = tb.objid JOIN tabledetails td ON tb.id = td.table_id LEFT JOIN information_schema.tables ist ON lower(td.table_name) = ist.table_name RIGHT JOIN iamuser ON db.owner_zuid = iamuser.zuid RIGHT JOIN iamuseremail iammail ON iammail.user_auto_id = iamuser.user_auto_id left join pg_stat_user_tables pgut on lower(td.table_name)=pgut.relname WHERE ist.table_schema = 'public' AND db.owner_zuid > 107 and pgut.n_live_tup is not null group by 1,4,5,7,10,12 ORDER BY 1 DESC;
Output
Note
If you wish to sort the table size data based on size, you can use the condition below.
Example:
Descending --> ORDER BY 11 DESC;
New to ADSelfService Plus?
Related Articles
Analytics Plus Inventory
Aim: To have the detailed list of all the Reports, Databases created in Analytics Open Local & Cloud Databases and fill the entries password = Postgres and if any error occurs stating something like "check the DB connection" change the Port to ...Query table throws error upon execute query - Linux - 4400 and above
Issue: Query table's 'execute query' will throw an error as shown below. Even when executing 'select 1', we might get this error. Build affected: 4400, 4410 and 4420 Only on Linux installations Cause: It is due to internal code changes and will be ...Steps to enable Google Analytics Connector
1. Stop the Analytics Plus services. 2. Modify the entry under <Analytics Plus home>/reports/conf/app.properties show.create.google.analytics.view=true Note: If the above property is missing, add them. By default the value will be set to 'false' 3. ...Query to Fetch Inactive Columns from a Workspace
Requirement: Customer wanted to determine the number of used or unused columns in a workspace. Solution: To achieve this, we need to connect to the Analytics Plus database by using local database import method (PostgrSQL) and then use the custom ...Query Reports not refreshing on Build 3600
Issue: Once migrated to 3600, the existing query reports do not get refreshed with latest data. Needs manual update. Solution: This seems to be breakage in 3600 builds. Open the <AnalyticsPlus\conf\ app.properties> file and then change the value ...