RE: Query is slow when run for first time; subsequent execution is fast
Hi, On 17 Jan 2018 12:55, "POUSSEL, Guillaume" wrote: Are you on Windows or Linux? I’m on Windows and wondering if the issue is the same on Linux? I have experienced this on Mac and Linux machines. You can try pg_prewarm, on pg_statistic table and its index. But I'd probably just put an entry in my db startup script to run this query immediately after startng the server, and let the query warm the cache itself. I will try this suggestion and get back on the thread. Is pg_statistic the only table to be pre cached? Pls let me know if any other table/index needs to be pre warmed. Btw, I don't running a "select * from pg_statistic" will fill the shared buffer. Only 256 kb of data will be cached during sequential scans. I will try pg_prewarm Why do you restart your database often Postgres is bundled with our application and deployed by our client. Starting / stopping the server is not under my control. Regards, Nanda
Re: Query is slow when run for first time; subsequent execution is fast
On Tue, Jan 16, 2018 at 09:18:25PM -0800, Jeff Janes wrote: > Oh. I've not seen that before. But then again I don't often restart my > server and then immediately run very large queries with a stringent time > deadline. > > You can try pg_prewarm, on pg_statistic table and its index. But I'd > probably just put an entry in my db startup script to run this query > immediately after startng the server, and let the query warm the cache > itself. > > Why do you restart your database often enough for this to be an issue? Another thing that you could use here is pg_buffercache which offers a way to look at the Postgres shared buffer contents in real-time: https://www.postgresql.org/docs/current/static/pgbuffercache.html As Jeff says, pg_prewarm is a good tool for such cases to avoid any kind of warmup period when a server starts.. -- Michael signature.asc Description: PGP signature
