We just experienced a huge speed improvement (x16 times) by running
ANALYZE on a few tables after debugging some queries with unusual plans using the
VISUAL EXPLAIN feature in MemSQL Studio.
The huge impact was probably caused by a larger data movement/regeneration yesterday, but it made us wonder if we should schedule some
ANALYZE queries to always keep the statistics up-to-date.
The documentation mentions some automatic statistics collections, but strongly recommends executing
ANALYZE queries manually.
We do mostly have
rowstores but also a single
columnstore with histograms.
I guess we would benefit from a daily/weekly statistics collection, but wonders why this isn’t happening automatically by MemSQL. The only real draw appears to be plan invalidation, which probably isn’t the worst drawback since you gave us the
Is this the right direction to follow, or am I missing something?