I struggle with this a lot with Marginalia's index. Where I identify a hot method in a prod profiler run, try to replicate it on a test machine where I can never get the same cache characteristics because everything in this space is like an onion of caching layers that you affect the real performance of the system. I may get it to run significantly faster, but that only sometimes makes the production profiler sample move its needle.
e.g. I've recently been experimenting with using a cursed hybrid model in Marginalia's index, where based on a mincore probe, I switch between mmap and io_uring for reading a cluster of pseudoadjacent data. There are real tanglible benefits both in the test machine and in prod with this, but the numbers do not agree at all about how the needle moves :P
I had an issue like this for optimizing pgrust. I had an optimization that showed no impact on my test machine (c8g.4xl) and showed a 20% improvement when ran on my mac. It turns out the issue was the instruction cache on the c8g.4xl was being saturated on the test machine but not on my laptop, moving the bottleneck to a different place
If you can consistently reproduce the performance difference, you're already half way there
The hard part about optimizing this type of code, IMO, is that there are so many cache layers, both in the CPU and the OS and sometimes in the storage medium. You can warm all of those caches up, but then you're testing a nonsense scenario that will basically never happen in a realistic scenario, where wall clock time is what matters, and not much the CPU is working or how many IOPS you're pushing.
A question on 20s postgresql time - It does not look like you are accounting for reading data from disk? Wouldn't the aggregation query have to load data from disk first? Or is it somewhat guaranteed that the table is already in memory? The Rust version is clearly in memory (I am no rust expert, so that may not even be actually in memory, if its a generator).
I choose the data size so that it would fit in memory on the machine I was testing on. fwiw, there's still a ton of overhead Postgres has that the toy example does not. For example Postgres will serialize the numbers into tuples and need to deserialize them to execute the query. That's why it's not an apples-to-apples comparison
AlloyDB from Google Cloud uses columnar storage like a secondary index, while the relations are still stored in TOAST.
Its unlikely an AI written project is one which people will want to maintain even if it "works", which means AI has to maintain it. Maintaining a code base of a production database is going to continue costing 100s of thousands, if its even possible at all with AI. Its also unlikely to find users even if its almost perfect, because a bug in a database is not something users can accept.
Postgres is postgres because its like 30 years old and battle tested. Sure its missing some modern features, but its incredibly stable, which is part of its popularity.