Slow DB Query: submitted_count_msme_woman_unique

[sentry-io[bot]]

Sentry Issue: CREDERE-BACKEND-PRODUCTION-26

The query:

SELECT COUNT(*) AS count_1
FROM (
  SELECT borrower.id AS borrower_id
  FROM borrower JOIN award ON award.borrower_id = borrower.id JOIN application ON
    application.borrower_id = borrower.id
  WHERE application.borrower_submitted_at IS NOT NULL AND borrower.size != %(size_1)s AND (
    award.source_data_contracts ->> %(source_data_contracts_1)s) IN (
    %(param_1_1)s, %(param_1_2)s
  )
  GROUP BY borrower.id
) AS anon_1

Looks like it matches:

    submitted_count_msme_woman_unique = (
        session.query(Borrower.id)
        .join(Award, Award.borrower_id == Borrower.id)
        .join(Application, Application.borrower_id == Borrower.id)
        .filter(Application.borrower_submitted_at.isnot(None))
        .filter(Borrower.size != BorrowerSize.BIG)
        .filter(Award.source_data_contracts["g_nero_representante_legal"].astext.in_(WOMAN_VALUES))
        .group_by(Borrower.id)
        .count()
    )

in get_borrower_opt_in_stats, which is called by:

• update_statistics (a background task, and a command that runs once a day - background tasks are run after returning a response so this issue gets reported in Sentry for the API endpoints that ran the background task)
• GET /statistics-ocp/opt-in

[jpmckinney]

Since this slow query is exclusive to an OCP-only endpoint, I think it's okay to leave as-is.

[sentry-io[bot]]

Sentry Issue: CREDERE-BACKEND-PRODUCTION-25

Same here:

Query (difference is borrower_accepted_at instead of borrower_submitted_at):

SELECT COUNT(*) AS count_1
FROM (
  SELECT borrower.id AS borrower_id
  FROM borrower JOIN award ON award.borrower_id = borrower.id JOIN application ON
    application.borrower_id = borrower.id
  WHERE application.borrower_accepted_at IS NOT NULL AND borrower.is_msme = true AND (
    award.source_data_contracts ->> %(source_data_contracts_1)s) IN (
    %(param_1_1)s, %(param_1_2)s
  )
  GROUP BY borrower.id
) AS anon_1

Which matches:

    accepted_count_msme_woman_unique = (
        session.query(Borrower.id)
        .join(Award, Award.borrower_id == Borrower.id)
        .join(Application, Application.borrower_id == Borrower.id)
        .filter(Application.borrower_accepted_at.isnot(None))
        .filter(Borrower.is_msme == true())
        .filter(Award.source_data_contracts["g_nero_representante_legal"].astext.in_(WOMAN_VALUES))
        .group_by(Borrower.id)
        .count()
    )

[jpmckinney]

The JOINs are now through application.award_id instead of award.borrower_id #336, so this might be faster now.