Ayiga
commented
3 months ago 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.
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