CODEWITHSUNDEEP
cognosgreenplum
yaron
yaron

Reputation: 383

cognos greenplum pg_catalog

I am using Cognos report and Greenplum database.

My report is taking too much time too run. So I looked at the green plum log, and discover that half of the time where spent querying the pg_catalog. In the catalog i am not looking at the pg_catalog but on other tables, so i don't understand why the cognos decide to look at the pg_catalog table.

When i run cognos report i can see from the greenplum log, that a for some of the query, cognos run select on pg_catalog before running the query.

Why is this?

Upvotes: 1

Views: 892

Answers (1)

mys
mys

Reputation: 2473

pg_catalog contains metadata about tables, columns and other database objects. I think your problem is catalog bloat. Basically, if you need to wait few seconds to list tables (using \d in psql) then you should vacuum your catalog.

Check "Chapter 19: Routine System Maintenance Tasks" - "Regular System Catalog Maintenance" in GP admin guide.

Greenplum recommends that you periodically run VACUUM on the system catalog to clear the space occupied by deleted objects. If numerous DROP statements are a part of regular database operations, it is safe and appropriate to run a system catalog maintenance procedure with VACUUM daily at off-peak hours. This can be done while the system is running and available. The following example script performs a VACUUM of the Greenplum Database system catalog:

#!/bin/bash
DBNAME="<database_name>"
VCOMMAND="VACUUM ANALYZE"
psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from pg_class a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'pg_catalog' and a.relkind='r'" $DBNAME | psql -a $DBNAME

Upvotes: 0

Related Questions