[Task]: Add filtering and update the request model of the search endpoint #1167
Assignees
Labels
project: grants.gov
Grants.gov Modernization tickets
Comments
chouinar
added a commit
that referenced
this issue
Mar 12, 2024
chouinar
added a commit
that referenced
this issue
Mar 18, 2024
## Summary Fixes #1167 ### Time to review: __15 mins__ ## Changes proposed Added filtering to the search endpoint, includes all but the query box parameter which has its own follow-up ticket Added utilities to help generate the search filter schema Added indexes to improve the performance of search (see additional info below for details) Extensive additions to the tests Added the ability to choose examples on the OpenAPI docs (included an example with no filters, and one with many) Fixed a bug in the Paginator for handling counts (will follow-up and fix in the template repo) ## Context for reviewers This change has been extensively tested, manually, and through an enormous amount of new unit tests. As the change was already getting quite large, a few things will be dealt with in follow-up tickets: * Query filtering: #1455 * Fixing logging formatting: #1466 * Additional order_by fields: #1467 For the filters, they're all `one_of` filters which means that only one of the supplied values needs to match for it to pass the where clause (literally the where clauses generate as `where table.column in (1, 2, 3)`). You can see an example query below. The agency filter is a bit odd as I made it a `startswith` style filter instead to handle the way agency codes get nested. We may want to adjust this further in the future, but this will at least technically handle hierarchies of agencies right now. ## Additional information I extensively tested the performance of the queries we run. I locally loaded in ~11k records using our factories (ran the `seed-local-db` script 300 times). With the API functioning, I make SQLAlchemy output the queries it ran and did an `EXPLAIN ANALYZE ...` on the big ones. I then added several indexes which improved the performance. The primary query of the API looks like this: ```sql SELECT opportunity.opportunity_id, opportunity.opportunity_number, opportunity.opportunity_title, opportunity.agency, opportunity.opportunity_category_id, opportunity.category_explanation, opportunity.is_draft, opportunity.revision_number, opportunity.modified_comments, opportunity.publisher_user_id, opportunity.publisher_profile_id, opportunity.created_at, opportunity.updated_at FROM opportunity JOIN current_opportunity_summary ON opportunity.opportunity_id = current_opportunity_summary.opportunity_id JOIN opportunity_summary ON current_opportunity_summary.opportunity_summary_id = opportunity_summary.opportunity_summary_id JOIN link_opportunity_summary_funding_instrument ON opportunity_summary.opportunity_summary_id = link_opportunity_summary_funding_instrument.opportunity_summary_id JOIN link_opportunity_summary_funding_category ON opportunity_summary.opportunity_summary_id = link_opportunity_summary_funding_category.opportunity_summary_id JOIN link_opportunity_summary_applicant_type ON opportunity_summary.opportunity_summary_id = link_opportunity_summary_applicant_type.opportunity_summary_id WHERE opportunity.is_draft IS FALSE AND(EXISTS ( SELECT 1 FROM current_opportunity_summary WHERE opportunity.opportunity_id = current_opportunity_summary.opportunity_id)) AND current_opportunity_summary.opportunity_status_id IN(1,2) AND link_opportunity_summary_funding_instrument.funding_instrument_id IN(1,2) AND link_opportunity_summary_funding_category.funding_category_id IN(1,3,20) AND link_opportunity_summary_applicant_type.applicant_type_id IN(1, 2, 13) AND((opportunity.agency ILIKE 'US-ABC%') OR(opportunity.agency ILIKE 'HHS%')) ORDER BY opportunity.opportunity_id DESC LIMIT 25 OFFSET 25 ``` Without any of the new indexes, `EXPLAIN ANALYZE` gives this a cost of ~1100 (non-specific unit). With the new indexes it becomes ~800. The actual runtime of these queries is in the 5-10ms range with or without the indexes, so it's minor either way. Note that querying the API locally, this gives response times of 50-150ms (slower initially before caching likely takes hold). Also if we're just filtering by something like opportunity status, then the costs are around 10-15. See: https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-ANALYZE --------- Co-authored-by: nava-platform-bot <platform-admins@navapbc.com>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Summary
From https://app.gitbook.com/o/cFcvhi6d0nlLyH2VzVgn/s/v1V0jIH7mb7Yb3jlNrgk/engineering/learnings/opportunity-endpoint-data-model#overview
Work with the front-end/design folks to figure out what filters we want to be certain to have, and implement them. We don't need to have anything elaborate, much later follow-up work will implement a search index
Acceptance criteria
The text was updated successfully, but these errors were encountered: