Explorer search queries are very slow

[jbearer]

The query planner can only use a B-tree index for pattern matching when the pattern is a constant anchored to the beginning of the string (e.g. 'query%'). Our patterns are anchored to the start of the string, but they are not constants, because we form them by concatenating the user's query with the % wildcard, like $1 || '%'. This avoids having to sanitize the user's query string ourselves. Unfortunately, this parameter substitution also prevents the optimizer from using an index. This makes queries very slow when there are not matches, because it has to scan the entire table to find out that there are no matches. Unfortunately for us, this is the case 100% of the time, because we always scan both the block and trasnaction tables, and a query that matches a block hash will never match any transaction hash, and vice versa

[Ayiga]

Annotating here for the record. In our discussions we discovered that if we eliminate the concat operation in the Query plan, we can still use a bind parameter in a prepare statement and it will still be effective.

[Ayiga]

Adding @imabdulbasit to this issue for reference.

The updated discussion on this is that the prefix search, regardless of how we do it will be slow. Additionally, it is a use-case we do not wish to support directly. As such, the search query should be replaced with a simple equality check.