-
Suggestion
-
Resolution: Unresolved
-
None
Problem Definition
On very large instance with millions of rows of data in the "AO_575BF5_DEV_SUMMARY" table queries such as the following may perform slowly and increase DB CPU utilization since we don't have any index for the UPDATED column:
select "AO_575BF5_DEV_SUMMARY"."ID", "AO_575BF5_DEV_SUMMARY"."ISSUE_ID", "AO_575BF5_DEV_SUMMARY"."CREATED", "AO_575BF5_DEV_SUMMARY"."UPDATED", "AO_575BF5_DEV_SUMMARY"."PROVIDER_SOURCE_ID", "AO_575BF5_DEV_SUMMARY"."JSON" from "AO_575BF5_DEV_SUMMARY" "AO_575BF5_DEV_SUMMARY" where "AO_575BF5_DEV_SUMMARY"."ISSUE_ID" = ? and "AO_575BF5_DEV_SUMMARY"."PROVIDER_SOURCE_ID" = ? order by "AO_575BF5_DEV_SUMMARY"."UPDATED" desc
select * from "AO_575BF5_DEV_SUMMARY" order by "UPDATED" desc
Currently we only have table index by default on "ID", "ISSUE_ID" and "PROVIDER_SOURCE_ID":
# \d+ "AO_575BF5_DEV_SUMMARY" Table "public.AO_575BF5_DEV_SUMMARY" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------------+-----------------------------+-----------+----------+-----------------------------------------------------+----------+--------------+------------- CREATED | timestamp without time zone | | not null | | plain | | ID | integer | | not null | nextval('"AO_575BF5_DEV_SUMMARY_ID_seq"'::regclass) | plain | | ISSUE_ID | bigint | | not null | 0 | plain | | JSON | text | | | | extended | | PROVIDER_SOURCE_ID | character varying(255) | | not null | | extended | | UPDATED | timestamp without time zone | | not null | | plain | | Indexes: "AO_575BF5_DEV_SUMMARY_pkey" PRIMARY KEY, btree ("ID") "index_ao_575bf5_dev567785983" btree ("ISSUE_ID") "index_ao_575bf5_dev996442447" btree ("PROVIDER_SOURCE_ID")
Suggested Solution
Add explicit index on the UPDATE column on "AO_575BF5_DEV_SUMMARY" table
Workaround
Manually add index on the UPDATE column for "AO_575BF5_DEV_SUMMARY" table:
CREATE INDEX index_ao_575bf5_dev_updated_index ON public."AO_575BF5_DEV_SUMMARY" USING btree ("UPDATED")
- links to
