MySQL has a really useful feature they call the query rewrite cache. The
optimizer checks incoming queries to see if a known better rewrite has been
placed within the query rewrite cache table. If one is found, the rewrite
replaces the incoming query before sending it to the execution engine. This
capability allows for one to fix poorly performing queries in 3rd party
application code that cannot be modified. For example, suppose a 3rd party
application contains the following inefficient query: SELECT COUNT(*) FROM
table WHERE SUBSTRING(column,1,3) = 'ABC'. One can place the following
rewrite in the query rewrite cache: SELECT COUNT(*) FROM table WHERE column
LIKE 'ABC%'. The original query cannot use an index while the rewrite can.
Since it's a 3rd party application there is really no other way to make
such an improvement. The existing rewrite rules in PostgreSQL are too
narrowly defined to permit such a substitution as the incoming query could
involve many tables, so what's needed is a general "if input SQL string
matches X then replace it with Y". This check could be placed at the
beginning of the parser.c code. Suggest that the matching code should first
check the string lengths and hash values before checking entire string
match for efficiency.