On Tue, Aug 9, 2016 at 6:27 PM, Suya Huang <shuang@connexity.com> wrote:
> Hi,
> I’ve got a SQL runs for about 4 seconds first time it’s been executed,but
> very fast (20ms) for the consequent runs. I thought it’s because that the
> first time table being loaded into memory. However, if you change the where
> clause value from “cat” to “dog”, it runs about 4 seconds as it’s never been
> executed before. Therefore, it doesn’t sound like the reason of table not
> being cached.
LIMIT clause operations combined with random access are particularly
sensitive to caching on slow media. The exact pages you want are
scattered around the dist but repeated scans of the same values will
pull up exactly the ones you want. You can warm the table assuming
your memory is sufficient enough to cache all the data you need.
Another (I think better-) plan is to buy media with faster random
access.
Are you using pg_trgm to index the 'name' field? gist/gin indexes are
*very* dependent on caching/fast drives as the indexes tend to be fat.
merlin