Add index to impove reaction migration performance (#20773)

[shawn111]

Summary

Without the database index, the migration performance is really bad. 3M posts couldn't finish migration in one hour. After add index, about 3 secs.

It seems to create an index help the performance a lot.

CREATE INDEX IF NOT EXISTS idx_posts_id_channel_id on posts (id, channelid);

After create index, update 3M posts cause 2~3 sec to update channelid.

mm=# UPDATE reactions SET channelid = COALESCE((select channelid from posts where posts.id = reactions.postid), '') WHERE channelid='';
UPDATE 74655
Time: 2185.623 ms (00:02.186)

Ticket Link

Fixes https://github.com/mattermost/mattermost-server/issues/20773

Release Note

• Database changes
  • add index

[mm-cloud-bot]

@shawn111: Adding the "do-not-merge/release-note-label-needed" label because no release-note block was detected, please follow our release note process to remove it.

I understand the commands that are listed here

[mattermod]

Hello @shawn111,

Thanks for your pull request! A Core Committer will review your pull request soon. For code contributions, you can learn more about the review process here.

Per the Mattermost Contribution Guide, we need to add you to the list of approved contributors for the Mattermost project.

Please help complete the Mattermost contribution license agreement? Once you have signed the CLA, please comment with /check-cla and confirm that the CLA check is green.

This is a standard procedure for many open source projects.

Please let us know if you have any questions.

We are very happy to have you join our growing community! If you're not yet a member, please consider joining our Contributors community channel to meet other contributors and discuss new opportunities with the core team.

[shawn111]

/check-cla

[agarciamontoro]

Let's put a hold on this until we finish the investigation in https://github.com/mattermost/mattermost-server/issues/20773

[mattermod]

This PR has been automatically labelled "stale" because it hasn't had recent activity. A core team member will check in on the status of the PR to help with questions. Thank you for your contribution!

[agarciamontoro]

Preemptively closing this PR, since it seems the reason is known in the issue (still waiting for confirmation).

[shawn111]

@agarciamontoro Thanks for support, after add back the primary key. It works.