6 to 7ms to scan 5 million rows (kinda) using a custom Rust database which is Postgres compatible
Hello everyone! I am excited to that I was able to achieve impossible query speeds!
This is the query, executed against 5 million rows:
SELECT
id, name, job_title, salary, department, age,
manager, location, hire_date, degree, skills,
current_project, performance_score, is_active, created_at,
updated_at, is_fired
FROM employees
WHERE
salary >= 249999.0
LIMIT 100
I took between 6 to 7 ms to find the correct rows.
You may ask how this was achieved without any sort of indexing or caching?
There is something called concurrent_processor in my codebase which does handle read queries. This is some sort of modular scheduler for different tasks which give you the final result.
If you simply instruct it to find salary >= 249999.0, it will scan sequentially all rows, which is not that slow, but it takes between 100 to 120ms.
The reason is because it does all the needed operations, such as reading, decoding, comparing and all that sorts of things 5 million times.
What if there is a guaranteed way to do this without having to scan 5 million rows? 😀
Here comes into play something like an interceptor within the concurrent_processor which tries it's best to decrease the rows needed for the scan, 100% of the time, and it's called:
Semantic Mapping Engine!
It consists of the scanner and of the engine itself. The scanner tries to scan the whole table, all 5 million rows to find the best possible rules that determine were the data is located.
After mapping and saving the data (takes from 4% space worst case scenario down to below 1% of the actual data) it allows for blazingly fast candidate creation.
These candidates are potential rows that might be fulfilling the query criteria, and finally the actual rows' data is processed, and the needed rows are returned.
This dramatically decreases query times.
It's still in alpha stage but anyone can try it now!