Performing the has:plural lookup is quite slow

[Geeyun-JY3]

Describe the issue

It takes quite a long time to perform the has:plural lookup at the language of a project level.
I cannot even load the page while performing the has:plural lookup at the project level and the language of an instance level.

I already tried

• I've read and searched the documentation.
• I've searched for similar issues in this repository.

Steps to reproduce the behavior

1. Go to search tab of a language of a project / a project / a language of an instance
2. Enter has:plural
3. Click on the Search button
4. Cannot load the page

Expected behavior

Everyone can open the corresponding page.

Screenshots

No response

Exception traceback

No response

How do you run Weblate?

weblate.org service

Weblate versions

Weblate 4.16-dev (weblate-4.15.2-209-gde41682274)

Weblate deploy checks

No response

Additional context

Example:

• It takes a long time to open https://hosted.weblate.org/languages/zh_Hans/weblate/search/?offset=1&q=has%3Aplural&sort_by=num_words&checksum=
• I cannot open https://hosted.weblate.org/search/weblate/?offset=&q=has%3Aplural&sort_by=-priority%2Cposition&checksum= and https://hosted.weblate.org/languages/zh_Hans/-/search/?offset=&q=+has%3Aplural&sort_by=-priority%2Cposition&checksum=

[nijel]

Confirmed on my test instance as well. Most likely this was caused by 50c5258 - the pg_trgm is great for text search, but performs poorly on whitespace search (what has:plural translates to) as it has to recheck every string. We need to have a fallback operator for whitespace only searches here...

Related: https://stackoverflow.com/questions/57683799/postgresql-using-pg-trgm-slower-then-full-scan

[github-actions]

Thank you for your report; the issue you have reported has just been fixed.

• In case you see a problem with the fix, please comment on this issue.
• In case you see a similar problem, please open a separate issue.
• If you are happy with the outcome, don’t hesitate to support Weblate by making a donation.

[Geeyun-JY3]

@nijel Performing a source:r"(^|\W)ID(\W|$)" lookup in the same level is quite slow too. Is it a similar bug?

[nijel]

Probably yes, though this one should not be that bad...

[Geeyun-JY3]

My primary use case is searching at the language of a project level. It still takes me several minutes to load the results listing page. I think this is unacceptable.
I think all the searching results listing page should be loaded in less than 1 minute.
While using this regular expression, it seems that if the specific word I want to search is less than three letters, performing this lookup would be quite slow.
Should I open an issue for it?

[nijel]

Yes, the string length is the key here (or actually the length of the alphanumeric part of the string). Unfortunately, getting that info from regular expression is not that straightforward, but I will look into options…

Weblate uses PostgreSQL trigram index to optimize these. They work great for 3+ alphanumeric chars, but horribly for shorter ones. Unfortunately, the PostgreSQL optimizer doesn't deal with this, and we work around this in Weblate for the strings by adding string concatenation to the field when the string is too short and would perform badly. Adding operation on the field stops PostgreSQL from using index, and resorts to full table scan, which is faster in this case.

[nijel]

#8840 should address this.