2 ms·

Was this because of a missing index or because the optimizer vomited on the queries without an index to provide it statistics? My gripe with RDBs is generally

Was this because of a missing index or because the optimizer vomited on the queries without an index to provide it statistics? My gripe with RDBs is generally the query optimizer is non deterministic with respect to the query, I.e., it can make a great decision with certain statistics and flip to a terrible one with slightly different statistics even though the original query would have performed basically the same. I’d rather have a database that query performance degrade gracefully and predictably with scale than some sort of black magic mumbo jumbo wake me up in the middle of the night because it lost its statistical little puny pea brain simply because it couldn’t compute a bounded expectation any more.

londons_explore 3y ago
I really want the optimizer to make an estimate of the CPU/IO to complete a query. Then, during the query, if that much effort has been expended and we haven't yet completed the query, then update the estimate. If the updated estimate now shows that the query plan is no longer quickest, then abort the query and restart with a different plan. Years ago I forked postgres and tried to implement the above. Initial results were promising - there were a good chunk of queries that ended up taking a different plan, and sometimes returning 100x quicker. Alas, the postgres codebase is heindously complex, and implementing the above to be production grade would be many months work - and, due to the way postgres streams results to the client, might have actually required a change to the wire format.
magicalhippo 3y ago
In this case it was a missing index, on a query run on every order line when saving aka a lot. It had gone under the radar because the volume with our other customers had been low enough that the table scan of the queried table wasn't noticed. As mentioned a customer suddenly got 1000x the volume and it quickly became an issue. But yea, we have a job running in the weekends to recalculate statistics on key tables, as we've had issues with that grinding production to a halt before. And recently I sped up a query by 100x by removing a filter from the where clause that for some weird reason caused the DB to run a really poor plan. It was just a simple check intended to filter out some duplicates in a few edge cases, but couldn't find a way to make the DB run it as a post-predicate. Moved it to my code for the 100x performance win...

londons_explore 3y ago

I really want the optimizer to make an estimate of the CPU/IO to complete a query. Then, during the query, if that much effort has been expended and we haven't yet completed the query, then update the estimate. If the updated estimate now shows that the query plan is no longer quickest, then abort the query and restart with a different plan. Years ago I forked postgres and tried to implement the above. Initial results were promising - there were a good chunk of queries that ended up taking a different plan, and sometimes returning 100x quicker. Alas, the postgres codebase is heindously complex, and implementing the above to be production grade would be many months work - and, due to the way postgres streams results to the client, might have actually required a change to the wire format.

magicalhippo 3y ago

In this case it was a missing index, on a query run on every order line when saving aka a lot. It had gone under the radar because the volume with our other customers had been low enough that the table scan of the queried table wasn't noticed. As mentioned a customer suddenly got 1000x the volume and it quickly became an issue. But yea, we have a job running in the weekends to recalculate statistics on key tables, as we've had issues with that grinding production to a halt before. And recently I sped up a query by 100x by removing a filter from the where clause that for some weird reason caused the DB to run a really poor plan. It was just a simple check intended to filter out some duplicates in a few edge cases, but couldn't find a way to make the DB run it as a post-predicate. Moved it to my code for the 100x performance win...