Add trigram index to snippet content
Problem to solve
In #26123 (closed) we're trying to investigate ways to improve the request time for snippets searches. We discovered that the main problem was the scope we were using (too wide).
We decided to, at least, reduce the scope for .com. Nevertheless, self-hosted installations will still run the current code and we can improve it by adding an index to the column content
in snippets
.
In .com, with warm caches, the result of one of the count queries involved is:
Cost: 154933.06
Time: 53.909 s
- planning: 39.449 ms
- execution: 53.870 s
- I/O read: 3.192 s
Shared buffers:
- hits: 473896 (~3.60 GiB) from the buffer pool
- reads: 130084 (~1016.30 MiB) from the OS file cache, including disk I/O
- dirtied: 2252 (~17.60 MiB)
- writes: 0
With a trigram index in the content
column the times are:
Cost: 4333.77
Time: 11.718 s
- planning: 42.159 ms
- execution: 11.676 s
- I/O read: 347.184 ms
Shared buffers:
- hits: 98099 (~766.40 MiB) from the buffer pool
- reads: 470 (~3.70 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
The decrease of the execution time, even for .com, is quite high. For self-hosted users, this index would end in really fast requests.
The index size in production is around 280MB.
Intended users
Self-hosted users and .com users.
What does success look like, and how can we measure that?
The database queries and also the request times will decrease.