Database Queries Slow Down with Large smReplicationStatus and smReplicationPolicy Tables

[artntek]

ADC Metacat was very slow (example: requests to https://arcticdata.io/metacat/d1/mn/v2/meta/doi%3A10.18739%2FA2DR2P803 taking around 9 seconds). We tracked this down to slow database queries:

SELECT pid, age(now(), query_start) AS duration, query
FROM pg_stat_activity
WHERE state != 'idle' AND usename='metacat'
ORDER BY duration DESC;


   pid   |    duration     |                                                      query
---------+-----------------+------------------------------------------------------------------------------------------------------------------
 2017010 | 00:00:06.996166 | select guid, member_node, status, date_verified from smReplicationStatus where guid = $1
 2018255 | 00:00:06.695571 | select guid, member_node, status, date_verified from smReplicationStatus where guid = $1
 2017060 | 00:00:03.096873 | select guid, policy, member_node from smReplicationPolicy where guid = $1 and policy = $2 order by policy_id ASC
 2018387 | 00:00:03.090728 | select guid, member_node, status, date_verified from smReplicationStatus where guid = $1

The smReplicationStatus table had 1025827 rows, and smReplicationPolicy had 600k+. We remedied it on the prod databse by adding indexes:

-- Index for smReplicationStatus
CREATE INDEX CONCURRENTLY smReplicationStatus_guid
ON smReplicationStatus(guid);

-- Composite index for smReplicationPolicy
CREATE INDEX CONCURRENTLY smReplicationPolicy_guid_policy
ON smReplicationPolicy(guid, policy);

Now we need to add these indexes to the metacat SQL used to create the database. (Note that the metacat SQL should not include CONCURRENTLY -- this was only to allow us to run the command on production without affecting performance there). So the final commands to be added are:

CREATE INDEX smReplicationStatus_guid
ON smReplicationStatus(guid);

CREATE INDEX smReplicationPolicy_guid_policy
ON smReplicationPolicy(guid, policy);

[artntek]

these index statements should be added to both:

• src/xmltables-postgres.sql, and also to
• a newly-created src/upgrade-db-to-3.3.1-postgres.sql file. This latter file should include an IF NOT EXISTS clause