How to Fix Missing Index in PostgreSQL (Possibly Leading to Slow PA Performance)
This article describes an issue which you may come across in versions before Vesper 6.0. It has been resolved in Vesper 6.1.
Issue
There is not a namespace condition in the pg_class query. If an index with that name exists in another schema (e.g., dev and qa), then Analytics will wrongfully assume the index does not need to be created. This significantly impacts the performance.
This issue happens only in very specific conditions, when there was a partition copy within the same DB, e.g. abc-qa -> abc-dev. DB level partition copying is usually restricted to dedicated customer instances where the production partition is copied to QA on a regular basis.
How to Find Out
Check if there are really any missing indexes.
Indexes in PostgreSQL are created based on the Datamart configuration. Fields marked as dimension should have corresponding indexes.
Example: In the DB table for the Product DS, we expect an index on the column representing the key field in the DS:Column | Type | Description ---------------+-----------------------------+--------------------------------- attribute1 | character varying(255) | ProductID/Product ID Index: "abc-dev_DMDS_Product_uniq_upsert_idx" UNIQUE, btree (attribute1)
Check if you see generally much worse performance in your environment after upgrade to Vesper 6.0.
If the index is missing, queries and hence jobs are slower, and as a knock-on effect, the DB connection limit can be reached where previously it was not.
How to Fix It
The issue is fixed in Vesper 6.1. But as indexes are created only when data is loaded, an additional step is required to remedy the missing indexes without delay.
The two steps to resolve are:
Deploy Vesper 6.1.
Run this command in the system partition
Set Partition: system
Set Real URL (modify abc-qa to relevant partition label):
system$redeployfcs/abc-qa/createIndices
Found an issue in documentation? Write to us.