File caching

Hi PT, Vertica uses the Linux file-system cache for caching large amounts of data, which sounds like what you are interested in here. The Linux file-system cache is largely self-tuning for most use cases, but you can find various documentation for it online. In general, we try to cache larger blocks of data not when you first read them, but before then, when you first write them out to disk. So there's no reason to re-read them at all, even for the first query; I'm therefore not surprised that there would be no change in performance between the first and second query. If your data size is in fact small enough that in-memory between-query caching would be helpful, I'm curious whether what you're seeing is actually an IO bottleneck -- what do your CPU and network utilizations look like? Adam

Thanks Adam! In that case what does 'select clear_caches()' do?

Hi PT, For CLEAR_CACHES(), Vertica does have some internal caches as well. However, they're generally intended to optimize for specific situations and/or small, frequently-accessed bits of data. (This may or may not change in the future; that's just how this functionality currently works.) You may note that the documentation on CLEAR_CACHES() recommends that, in addition to calling CLEAR_CACHES(), you also/separately explicitly clear the Linux filesystem cache: https://my.vertica.com/docs/6.1.x/HTML/index.htm#20285.htm (Vertica does not clear this cache automatically for you because doing so requires Linux root privileges, which Vertica doesn't hold while running by default, and it potentially has a major performance impact on any non-Vertica processes running on the same machine.) Adam