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.
