Missing SQL indexes

[jpmckinney]

Can maybe start with the slow queries listed at https://open-contracting-partnership.sentry.io/performance/?project=4505799907672064&statsPeriod=14d

For single-column indexes, would need to add index=True to Field() calls to add these indexes.

Not sure if https://sqlmodel.tiangolo.com supports composite indexes. Might need to do SQLAlchemy or Alembic directly.

JOIN

Found using:

(?<!os\.path)\.join\(

• Application
  • [x] borrower_id #185
  • [x] award_id #185
  • [ ] credit_product_id (in statistics_utils.py only)
  • [ ] lender_id (in statistics_utils.py only)
• CreditProduct
  • [x] lender_id #185
• ApplicationAction
  • [ ] application_id

WHERE

Found using these regexes:

\.filter\((?:\n +)?\b(?:\w+\.)+(?!id\b\s)\w+\b\s
\.filter\((?:\n +)?\b(?:and_\(|or_\(|cast\(|text\()
\.filter\((?!(?:\n +)?\S+\.\w+)
\.where\(

To avoid a ton of indexes, I grouped some WHERE matches under one composite index.

Application

• [ ] (status, archived_at, lender_id)
  • status: get_all_applications_with_status()
  • (status, archived_at): get_all_active_applications()
  • (status, archived_at, lender_id): get_all_fi_applications_emails(), get_all_FI_user_applications()
• [ ] (award_borrower_identifier, status)
  • award_borrower_identifier: get_existing_application()
  • (award_borrower_identifier, status): get_previous_lenders(), get_previous_documents()
• [ ] (id, award_id, archived_at): remove_dated_data()

ApplicationAction

• [ ] (application_id, type): get_modified_data_fields(), check_if_application_was_already_copied(), get_application_days_passed()

Award

• [ ] (previous, borrower_id): get_previous_awards()
• [x] source_contract_id: get_existing_award() #185

Borrower

• borrower_identifier: get_borrower() (already has unique=True)

BorrowerDocument

• [ ] (application_id, type)
  • application_id: download_application()
  • (application_id, type): create_or_update_borrower_document(), get_previous_documents()

CreditProduct

• [ ] (lender_id, borrower_size, lower_limit, upper_limit, type)
  • (lender_id, borrower_size, lower_limit, upper_limit): reject_application()
  • (lender_id, borrower_size, lower_limit, upper_limit, type): credit_product_options()

Message

• [ ] type: get_applications_to_remind_submit(), get_applications_to_remind_intro()

User

• email: login(), login_mfa() (already has unique=True)
• [x] external_id: me(), OCP_only(), get_user() #185

get_general_statistics() and get_msme_opt_in_stats() have a lot of filters, but not sure how important to optimize these functions. I didn't add some from statistics.py or update_statistic.py.

I think the queries in application_utils.py are too complex for an index.

[jpmckinney]

Note that creating automatic migrations incorrectly detects nullable changes. These seem to be fixed in more recent 1.4.x versions of SQLAlchemy, but we need SQLModel to release 0.0.9 to unpin SQLAlchemy: https://github.com/tiangolo/sqlmodel/issues/434#issuecomment-1775601658