Speed up search queries

[abchrisxyz]

Search queries behind https://api.ergoplatform.com/search?query= currently look for partial matches e.g. where address like '%<address>%'.

I think most times people will search for a full address/tx_id/header_id anyway, so could be made much faster using exact matches: where address = '<address>' .

Happy to submit a PR if you guys are happy to drop partial matching functionality.

[reqlez]

Yea... I tried pretty much everything, except moving this DB to a high GHZ processor.
Here is an example query, that times out 90% of the time. Keep in mind I'm now allocating 12GB for the shared buffers, and this is running on an NVMe SSD + performed other tuning as well to get as much caching as possible.

https://ergo-explorer.anetabtc.io/en/search?query=9fDDX4RiHJqezYmUMvfpn9RXP4bYwr3NnosUYpVNKuvuFhzPm7i

I do not see how this is going to be sustainable once the database grows larger, it will only get worse. Just like what happened with the rich list.

I also tried to run Dexter, to look for anything that would benefit additional indexes, added this:
CREATE INDEX "node_outputs__global_index" ON node_outputs (global_index);

It helped with some queries, but ... not for this one.