Consider adding an index to pull_request_files on the repo_id

[cdolfi]

Consider adding index on repo_id to all the main tables

[sgoggins]

The repo_id is a foreign key nearly everywhere it is used outside of the repo table itself.

Adding an index here makes sense. Are there other tables where its also possibly useful?

[cdolfi]

check on pull_request_reviews as well

[cdolfi]

Check on all the main tables if there is an index on repo_id

[PredictiveManish]

@MoralCode Sir I think this can be closed now as #3184 is mentioned

[MoralCode]

Looking at the source code behind that PR, it doesnt seem to add an index - it just adds a unique constraint to some of the columns.

I believe this still needs to be done

[shlokgilda]

@MoralCode I believe adding a unique constraint in Postgres also makes it a unique index: https://www.postgresql.org/docs/current/indexes-unique.html. From the doc:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

[MoralCode]

that seems to check out, even on postgres 16 (the version we are on). Closing.