Add database index for "UPDATED" column on the "AO_575BF5_DEV_SUMMARY" table

XMLWordPrintable

    • 3

      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")

            Assignee:
            Unassigned
            Reporter:
            Suddha
            Votes:
            16 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated: