After migrating from patchwork 2.2 to 3.1, search by hash become too slow

[mchehab]

This happens via pwclient and using curl:

$ time curl -s 'https://patchwork.linuxtv.org/api/1.3/patches/?per_page=5&hash=b842eaa572bb99d576c4c9a814c0a7956c07c804'
[{"id":98147,"url":"https://patchwork.linuxtv.org/api/1.3/patches/98147/","web_url":"https://patchwork.linuxtv.org/project/linux-media/patch/87h6kfjay7.fsf@meer.lwn.net/","project":{"id":1,"url":"https://patchwork.linuxtv.org/api/1.3/projects/1/","name":"Linux Media kernel patches","link_name":"linux-media","list_id":"linux-media.vger.kernel.org","list_email":"linux-media@vger.kernel.org","web_url":"https://linuxtv.org","scm_url":"git://linuxtv.org/media_tree.git","webscm_url":"https://git.linuxtv.org/media_tree.git","list_archive_url":"https://lore.kernel.org/linux-media/","list_archive_url_format":"https://lore.kernel.org/linux-media/{}","commit_url_format":""},"msgid":"<87h6kfjay7.fsf@meer.lwn.net>","list_archive_url":"https://lore.kernel.org/linux-media/87h6kfjay7.fsf@meer.lwn.net","date":"2023-12-19T00:24:48","name":"[1/3] staging: media: ipu3: Remove some excess struct member documentation","commit_ref":null,"pull_url":null,"state":"accepted","archived":false,"hash":"b842eaa572bb99d576c4c9a814c0a7956c07c804","submitter":{"id":51,"url":"https://patchwork.linuxtv.org/api/1.3/people/51/","name":"Jonathan Corbet","email":"corbet@lwn.net"},"delegate":{"id":4491,"url":"https://patchwork.linuxtv.org/api/1.3/users/4491/","username":"sailus","first_name":"Sakari","last_name":"Ailus","email":"sakari.ailus@iki.fi"},"mbox":"https://patchwork.linuxtv.org/project/linux-media/patch/87h6kfjay7.fsf@meer.lwn.net/mbox/","series":[{"id":11920,"url":"https://patchwork.linuxtv.org/api/1.3/series/11920/","web_url":"https://patchwork.linuxtv.org/project/linux-media/list/?series=11920","date":"2023-12-19T00:24:48","name":"[1/3] staging: media: ipu3: Remove some excess struct member documentation","version":1,"mbox":"https://patchwork.linuxtv.org/series/11920/mbox/"}],"comments":"https://patchwork.linuxtv.org/api/patches/98147/comments/","check":"pending","checks":"https://patchwork.linuxtv.org/api/patches/98147/checks/","tags":{},"related":[]}]
real    3m25,298s
user    0m0,045s
sys 0m0,013s

[mchehab]

It turns that there's no index for hashes there:

EXPLAIN SELECT * FROM patchwork_patch where hash='b842eaa572bb99d576c4c9a814c0a7956c07c804';
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | patchwork_patch | ALL  | NULL          | NULL | NULL    | NULL | 49917 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.001 sec)

After manually running:

CREATE INDEX idx_patch_hash on patchwork_patch (hash);

The issue was fixed:

 SELECT id FROM patchwork_patch where hash='b842eaa572bb99d576c4c9a814c0a7956c07c804';
+-------+
| id    |
+-------+
| 98147 |
+-------+
1 row in set (0.002 sec)

EXPLAIN SELECT * FROM patchwork_patch where hash='b842eaa572bb99d576c4c9a814c0a7956c07c804';
+------+-------------+-----------------+------+----------------+----------------+---------+-------+------+-----------------------+
| id   | select_type | table           | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+------+-------------+-----------------+------+----------------+----------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | patchwork_patch | ref  | idx_patch_hash | idx_patch_hash | 121     | const | 1    | Using index condition |
+------+-------------+-----------------+------+----------------+----------------+---------+-------+------+-----------------------+
1 row in set (0.001 sec)

It seems that somewhere at Django model, a hash index is needed.

[stephenfin]

Patch submitted here.