PostgreSQL database Schema optimization

If you are working over a PostgreSQL database and notice a poor performance while opening a project, or specification, or even just when navigating through your project, you should execute the following query over your Visure database that modifies the Elements view.

NOTE: These changes only affect the database, therefore, it´s not necessary to stop the Visure server to implement these changes. However, it is highly recommended to perform a database backup before implementing any modifications in the database schema.

1. Open PGAdmin

2. Navigate to your database and expand it to display the Schemas.

3. Note the Schema name that you used when you created the database. In the following picture, the schema name would be VisureDB_chema. The public schema is a generic one that should not be used.

In the following query, replace [DATABASE_SCHEMA_NAME] by your schema name, making sure to keep the quotation marks, e.g. "VisureDB_schema"."PARTITIONS".

NOTE: PostgreSQL is case sensitive, so VisureDB_Schema (with a capital "S") will not find a schema called VisureDB_schema.

SELECT '(0,1)'::tid AS ctid,
 "PAR"."PROJECT" AS "PRJ_ID",
 "R_BAS_VER"."BASELINE" AS "BAS_ID",
 "PAR"."ID" AS "PAR_ID",
 "ELE"."ELEMS_TYPE" AS "ELE_TYPE",
 "ELE"."PARENT" AS "PARENT_ID",
 "ELE"."ID" AS "ELE_ID",
 "ELE"."SPECIAL_ID",
 "ELE"."UUID" AS "ELE_UUID",
 "VER"."ID" AS "VER_ID",
 "VER"."CODE" AS "VER_CODE",
 "VER"."VER_NAME",
 "VER"."UUID" AS "VER_UUID",
 "ELE"."CREATION_DATE" AS "ELE_CD",
 "ELE"."CREATION_USER" AS "ELE_CU",
 "VER"."CREATION_DATE" AS "VER_CD",
 "VER"."CREATION_USER" AS "VER_CU",
 "VER"."LAST_MODIFICATION_DATE" AS "VER_LMD",
 "VER"."LAST_MODIFICATION_USER" AS "VER_LMU",
 "VER"."NVERSION",
 "ELE"."NVERSIONS",
 "VER"."COLOR",
 "DESCS"."ID" AS "DESC_ID",
 "DESCS"."CRC" AS "DESC_CRC",
 "DESCS"."UNCOMPRESSED_SIZE" AS "DESC_USIZE",
 "R_BAS_VER"."ORDER_ID" AS "ELE_ORDER_ID",
 "CHK"."CREATION_DATE" AS "CHK_CD",
 "CHK"."CHK_USER"
FROM "[DATABASE_SCHEMA_NAME]"."PARTITIONS" "PAR"
 JOIN "[DATABASE_SCHEMA_NAME]"."R_PAR_VER" ON "PAR"."ID" = "R_PAR_VER"."PARTITION_ID"
 JOIN "[DATABASE_SCHEMA_NAME]"."VERSIONS" "VER" ON "VER"."ID" = "R_PAR_VER"."VERSION_ID"
 JOIN "[DATABASE_SCHEMA_NAME]"."ELEMENTS" "ELE" ON "ELE"."ID" = "VER"."ELEMENT"
 JOIN "[DATABASE_SCHEMA_NAME]"."DESCRIPTIONS" "DESCS" ON "VER"."ID" = "DESCS"."VERSION_ID"
 JOIN "[DATABASE_SCHEMA_NAME]"."R_BAS_VER" ON "VER"."ID" = "R_BAS_VER"."VERSION_ID"
 LEFT JOIN "[DATABASE_SCHEMA_NAME]"."CHECKED_OUTS" "CHK" ON "ELE"."ID" = "CHK"."ELEMENT"
WHERE "[DATABASE_SCHEMA_NAME]"."R_BAS_VER"."STATUS" = 1;

4. Right-click on the database and select Query Tool.

5. Paste the modified script that includes the correct schema name.

6. Execute the script by clicking on the Execute button.

7. If the script was successful, you will get a confirmation message: