Excessive itemrevision db queries in 1.1.2 resulting in poor auditor performance

[eltair]

Please make sure that you have checked the boxes:

• Pease review the Troubleshooting doc for additional details regarding your issue.
• Review the Quickstart guide
• Search for both open and closed issues regarding the problem you are experiencing
• For permissions issues (Access Denied and credential related errors), please refer to the requisite docs before submitting an issue:
  AWS, GCP, OpenStack, GitHub

Description of issue:

A single execution of find_changes (as shown below) against a single tech results in over 44 thousand sql queries, most of which are very similar. This results in poor performance for the audit. Using a fresh database improves performance dramatically.

python manage.py find_changes -a <account censored> -m securitygroup

I turned on statement logging in postgres and captured the statements generated by the above command:

$ grep SELECT postgresql.log.2018-05-24-22 | cut -d ' ' -f 6- | sort | uniq -c | sort -n
      1 SELECT auditorsettings.id AS auditorsettings_id, auditorsettings.disabled AS auditorsettings_disabled, auditorsettings.issue_text AS auditorsettings_issue_text, auditorsettings.auditor_class AS auditorsettings_auditor_class, auditorsettings.tech_id AS auditorsettings_tech_id, auditorsettings.account_id AS auditorsettings_account_id 
      1 SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
      1 SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
      1 SELECT ignorelist.id AS ignorelist_id, ignorelist.prefix AS ignorelist_prefix, ignorelist.notes AS ignorelist_notes, ignorelist.tech_id AS ignorelist_tech_id 
      1 SELECT networkwhitelist.id AS networkwhitelist_id, networkwhitelist.name AS networkwhitelist_name, networkwhitelist.notes AS networkwhitelist_notes, networkwhitelist.cidr AS networkwhitelist_cidr 
      1 SELECT technology.id AS technology_id, technology.name AS technology_name 
      1 SELECT t.oid, typarray
      1 SELECT 'x' AS some_label
      7 SELECT account_type.id AS account_type_id, account_type.name AS account_type_name 
     25 SELECT itemaudit.id AS itemaudit_id, itemaudit.score AS itemaudit_score, itemaudit.issue AS itemaudit_issue, itemaudit.notes AS itemaudit_notes, itemaudit.action_instructions AS itemaudit_action_instructions, itemaudit.background_info AS itemaudit_background_info, itemaudit.origin AS itemaudit_origin, itemaudit.origin_summary AS itemaudit_origin_summary, itemaudit.class_uuid AS itemaudit_class_uuid, itemaudit.fixed AS itemaudit_fixed, itemaudit.justified AS itemaudit_justified, itemaudit.justified_user_id AS itemaudit_justified_user_id, itemaudit.justification AS itemaudit_justification, itemaudit.justified_date AS itemaudit_justified_date, itemaudit.item_id AS itemaudit_item_id, itemaudit.auditor_setting_id AS itemaudit_auditor_setting_id 
     26 SELECT itemauditscores.id AS itemauditscores_id, itemauditscores.technology AS itemauditscores_technology, itemauditscores.method AS itemauditscores_method, itemauditscores.score AS itemauditscores_score, itemauditscores.disabled AS itemauditscores_disabled 
     27 (EXISTS (SELECT 1 
     27 SELECT "user".id AS user_id, "user".email AS user_email, "user".password AS user_password, "user".active AS user_active, "user".confirmed_at AS user_confirmed_at, "user".daily_audit_email AS user_daily_audit_email, "user".change_reports AS user_change_reports, "user".last_login_at AS user_last_login_at, "user".current_login_at AS user_current_login_at, "user".login_count AS user_login_count, "user".last_login_ip AS user_last_login_ip, "user".current_login_ip AS user_current_login_ip, "user".role AS user_role 
     50 SELECT watcher_config.id AS watcher_config_id, watcher_config.index AS watcher_config_index, watcher_config.interval AS watcher_config_interval, watcher_config.active AS watcher_config_active 
     76 SELECT item.id AS item_id, item.region AS item_region, item.name AS item_name, item.arn AS item_arn, item.latest_revision_complete_hash AS item_latest_revision_complete_hash, item.latest_revision_durable_hash AS item_latest_revision_durable_hash, item.tech_id AS item_tech_id, item.account_id AS item_account_id, item.latest_revision_id AS item_latest_revision_id 
   1061 SELECT account.id AS account_id, account.active AS account_active, account.third_party AS account_third_party, account.name AS account_name, account.notes AS account_notes, account.identifier AS account_identifier, account.account_type_id AS account_account_type_id 
   1634 SELECT account_type_values.id AS account_type_values_id, account_type_values.name AS account_type_values_name, account_type_values.value AS account_type_values_value, account_type_values.account_id AS account_type_values_account_id 
  20634 SELECT itemrevision.config AS itemrevision_config 
  20638 SELECT itemrevision.id AS itemrevision_id, itemrevision.active AS itemrevision_active, itemrevision.date_created AS itemrevision_date_created, itemrevision.date_last_ephemeral_change AS itemrevision_date_last_ephemeral_change, itemrevision.item_id AS itemrevision_item_id

[mikegrima]

I recently added more querying mostly to address some SQLAlchemy stability issues.

Would welcome some assistance to help streamline.