Improve performance of runner tag search query

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

The following discussion from !166508 (merged) should be addressed:

• @fcatteau started a discussion: (+1 comment)

  @mc_rocha I'm approving this MR since changing the tag matching from = to LIKE doesn't increase the complexity of the database query.

  However, on postgres.ai it took about 20 seconds to run these queries with a cold cache. (Hopefully it only took 200ms to run the same queries with a warm cache.) So I suggest we create a follow-up issue to address that perform issue. Again, that's not related to the change.

  Note: There's a gin_trgm_ops index on tags.name but in the query plans you've shared it's not even used since tags are filtered using the taggings table.

  By the way, looking at the query plans you've shared it seems that you first ran = 'gitlab-org' query (~20s) and then the LIKE '%unner%' query (~200ms). I did the opposite and got opposite results: 20s for = 'gitlab-org', and 200ms for LIKE '%unner%'. In the future you might want to get query plans for a cold cache and a warm cache.

  @a_akgun Could you give this a database maintainer review?

  Do you have suggestions to improve the original query?