Scripts to verify required indexes exist for mod-source-record-storage

Ramsons and Quesnelia CSP releases include changes to optimize the clean up job of marc_indexers table (https://folio-org.atlassian.net/browse/MODSOURCE-834, https://folio-org.atlassian.net/browse/MODSOURCE-855). In case high spikes of CPU usage on DB is still observed for mod-source-record-storage, verify the following indexes:

idx_marc_indexers_marc_id_002 (“002“ - partition number) - for marc_indexers table
idx_marc_records_tracking_dirty - for marc_records_tracking.

Regarding idx_marc_indexers_marc_id_002 index.
Expected index: 'create index idx_marc_indexers_marc_id_version_002 on marc_indexers_002 (marc_id, version);'
Actual index: 'create index idx_marc_indexers_marc_id_002 on marc_indexers_002 (marc_id);'
To verify if this index is valid on the tenant, use this script

SELECT 
    n.nspname as schema_name,
    i.relname as index_name,
    a.attname as column_name,
    idx.indisunique as is_unique,
    idx.indisprimary as is_primary
FROM 
    pg_class t
    JOIN pg_index idx ON t.oid = idx.indrelid
    JOIN pg_class i ON i.oid = idx.indexrelid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(idx.indkey)
    JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE 
    t.relname = 'marc_indexers_002'
    AND n.nspname = '{tenant_id}_mod_source_record_storage';

Investigate the results, and if there is an index JUST FOR 1 column -“marc_id“ column - then it seems like this index should be re-created with 2 column - “marc_id“ and “version“.

Re-create this index using the script (it will remove the invalid index in each partition and create a new, valid one):

do $$ declare
    index integer;
    suffix text;
begin
  for index in 0 .. 999 loop
    suffix = lpad(index::text, 3, '0');
    execute 'drop index if exists ${database.defaultSchemaName}.idx_marc_indexers_marc_id_' || suffix || ';';
    raise info 'Starting to create index "${database.defaultSchemaName}.idx_marc_indexers_marc_id_version_%"', suffix;
    execute 'create index if not exists idx_marc_indexers_marc_id_version_' || suffix || ' on ${database.defaultSchemaName}.marc_indexers_' || suffix || '(marc_id, version);';
    raise info 'Index "${database.defaultSchemaName}.idx_marc_indexers_marc_id_version_%" has been created', suffix;
  end loop;
end; $$;

If there are 2 columns - “marc_id“ and “version” - no need to do smth here.

Regarding idx_marc_records_tracking_dirty index:
There can be a missing “idx_marc_records_tracking_dirty“ index inside the mod-source-record-storage module for some tenants.
To verify if it exists, use this script:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'marc_records_tracking' AND schemaname = '{tenant_id}_mod_source_record_storage';

If index “idx_marc_records_tracking_dirty“ exists - no need to do smth here.
If there is NO index(idx_marc_records_tracking_dirty) in that table - feel free to use this script to create this index:

CREATE INDEX IF NOT EXISTS idx_marc_records_tracking_dirty ON ${database.schemaName}.marc_records_tracking USING btree (is_dirty);