reqlez
commented
1 year ago 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.
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.
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.