On Tue, Aug 9, 2016 at 6:27 PM, Suya Huang <shuang@xxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
