Re: Query is slow when run for first time; subsequent execution is fast
On Tue, Sep 4, 2018 at 3:16 AM jimmy wrote: > On windows, how to put an entry in my db startup script to run this query > （pg_prewarm） immediately after startng the server, and let the query warm > the cache itself. > Starting with PostgreSQL version 11 (to be released soon), you can use pg_prewarm.autoprewarm. Until then, maybe this: https://superuser.com/questions/502160/run-a-scheduled-task-after-a-windows-service-is-started I've tested neither one. Cheers, Jeff
RE: Query is slow when run for first time; subsequent execution is fast
On windows, how to put an entry in my db startup script to run this query （pg_prewarm） immediately after startng the server, and let the query warm the cache itself. After starting the server, I want to know what is the server, and it is the database I restarted or windows system? Thank you. >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
Hi,
I tried pg_prewarm as suggested by Jeff Janes and it works - thanks a lot
Jeff. Now the query planning is fast on the first execution.
Here is the list of tables that needed to be pre warmed (or you could just
pre warm all the 'pg_%' tables. :-) ).
select pg_prewarm('pg_statistic');
select pg_prewarm('pg_trigger_tgrelid_tgname_index');
select pg_prewarm('pg_trigger');
select pg_prewarm('pg_statistic_relid_att_inh_index');
select pg_prewarm('pg_index_indrelid_index');
select pg_prewarm('pg_index_indexrelid_index');
select pg_prewarm('pg_index');
select pg_prewarm('pg_constraint_conrelid_index');
select pg_prewarm('pg_constraint');
select pg_prewarm('pg_class_relname_nsp_index');
select pg_prewarm('pg_class_oid_index');
select pg_prewarm('pg_attribute_relid_attnum_index');
select pg_prewarm('pg_attribute');
select pg_prewarm('pg_attrdef_adrelid_adnum_index');
select pg_prewarm('pg_attrdef');
select pg_prewarm('pg_amproc_fam_proc_index');
select pg_prewarm('pg_namespace_oid_index');
Regards,
Nanda
On 18 Jan 2018 07:25, "Michael Paquier" wrote:
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
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
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
Hello, FWIW, I do have the same issue. Unfortunately our application is running on a standard laptop/desktop computers, not dedicated servers. Restarting the computer leads to a restart of the database server, which slow down all queries for several minutes. Are you on Windows or Linux? I’m on Windows and wondering if the issue is the same on Linux? BR, Guillaume De : Jeff Janes [mailto:jeff.ja...@gmail.com] Envoyé : mercredi 17 janvier 2018 06:18 À : Nandakumar M Cc : pgsql-performa. Objet : Re: Query is slow when run for first time; subsequent execution is fast On Fri, Jan 12, 2018 at 12:03 AM, Nandakumar M mailto:m.nand...@gmail.com> > wrote: Hello Jeff, Thanks for the insights. >Don't keep closing and reopening connections. Even if I close a connection and open a new one and execute the same query, the planning time is considerably less than the first time. Only when I restart the Postgres server then I face high planning time again. 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? Cheers, Jeff smime.p7s Description: S/MIME cryptographic signature This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
Re: Query is slow when run for first time; subsequent execution is fast
On Fri, Jan 12, 2018 at 12:03 AM, Nandakumar M wrote: > Hello Jeff, > > Thanks for the insights. > > >Don't keep closing and reopening connections. > > Even if I close a connection and open a new one and execute the same > query, the planning time is considerably less than the first time. Only > when I restart the Postgres server then I face high planning time again. > 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? Cheers, Jeff
Re: Query is slow when run for first time; subsequent execution is fast
Nandakumar M schrieb am 12.01.2018 um 09:03:
> Even if I close a connection and open a new one and execute the same
> query, the planning time is considerably less than the first time.
> Only when I restart the Postgres server then I face high planning
> time again.
Yes, because the data is cached by Postgres ("shared_buffers") and the
filesystem.
Fwd: Re: Query is slow when run for first time; subsequent execution is fast
Missed to have mailing list in to address.. forwarding now. -- Forwarded message -- From: "Nandakumar M" Date: 15 Jan 2018 12:16 Subject: Re: Query is slow when run for first time; subsequent execution is fast To: "Pavel Stehule" Cc: Hi, On Fri, Jan 12, 2018 at 3:34 PM, Pavel Stehule wrote: > > >> maybe some your indexes and some system tables are bloated. Try you run > VACUUM FULL ANALYZE > Tried this suggestion. Planning time gets reduced slightly but it is still way higher on the first run compared to subsequent runs of the same query. Regards, Nanda
Re: Query is slow when run for first time; subsequent execution is fast
2018-01-12 9:03 GMT+01:00 Nandakumar M : > Hello Jeff, > > Thanks for the insights. > > >Don't keep closing and reopening connections. > > Even if I close a connection and open a new one and execute the same > query, the planning time is considerably less than the first time. Only > when I restart the Postgres server then I face high planning time again. > > >The query plan itself is not cached, but all the metadata about the > (large number) of tables used in the query is cached. Apparently > reading/parsing that data is the slow step, not coming up with the actual > plan. > > I enabled logging for parser, planner etc in postgresql.conf and re run > the queries. Following is the logs - I am not sure exactly how this should > be read, but the major difference in elapsed time seems to be in PLANNER > STATISTICS section. > > -- start -- > > 1. First run > > LOG: PARSER STATISTICS > DETAIL: ! system usage stats: > ! 0.000482 elapsed 0.000356 user 0.000127 system sec > ! [0.004921 user 0.004824 sys total] > ! 0/0 [0/1] filesystem blocks in/out > ! 0/102 [0/1076] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [3/5] messages rcvd/sent > ! 0/0 [8/11] voluntary/involuntary context switches > STATEMENT: SELECT COUNT(*) FROM ChangeDetails LEFT JOIN SDOrganization > AaaOrg ON ChangeDetails.SITEID=AaaOrg.ORG_ID LEFT JOIN > ApprovalStatusDefinition ON ChangeDetails.APPR_STATUSID= > ApprovalStatusDefinition.STATUSID LEFT JOIN CategoryDefinition ON > ChangeDetails.CATEGORYID=CategoryDefinition.CATEGORYID LEFT JOIN > Change_Fields ON ChangeDetails.CHANGEID=Change_Fields.CHANGEID LEFT JOIN > Change_StageDefinition ON > ChangeDetails.WFSTAGEID=Change_StageDefinition.WFSTAGEID > LEFT JOIN Change_StatusDefinition ON ChangeDetails.WFSTATUSID= > Change_StatusDefinition.WFSTATUSID LEFT JOIN AaaUser ChangeManager ON > ChangeDetails.CHANGEMANAGERID=ChangeManager.USER_ID LEFT JOIN AaaUser > ChangeOriginator ON ChangeDetails.INITIATORID=ChangeOriginator.USER_ID > LEFT JOIN AaaUser ChangeOwner ON > ChangeDetails.TECHNICIANID=ChangeOwner.USER_ID > LEFT JOIN ChangeResolution ON ChangeDetails.CHANGEID=ChangeResolution.CHANGEID > LEFT JOIN ChangeTemplate ON ChangeDetails.TEMPLATEID=ChangeTemplate.TEMPLATEID > LEFT JOIN ChangeToClosureCode ON > ChangeDetails.CHANGEID=ChangeToClosureCode.CHANGEID > LEFT JOIN Change_ClosureCode ON ChangeToClosureCode.ID=Change_ClosureCode.ID > LEFT JOIN ChangeTypeDefinition ON ChangeDetails.CHANGETYPEID= > ChangeTypeDefinition.CHANGETYPEID LEFT JOIN ChangeWF_Definition ON > ChangeDetails.WFID=ChangeWF_Definition.ID LEFT JOIN ImpactDefinition ON > ChangeDetails.IMPACTID=ImpactDefinition.IMPACTID LEFT JOIN ItemDefinition > ON ChangeDetails.ITEMID=ItemDefinition.ITEMID LEFT JOIN > PriorityDefinition ON ChangeDetails.PRIORITYID=PriorityDefinition.PRIORITYID > LEFT JOIN QueueDefinition ON ChangeDetails.GROUPID=QueueDefinition.QUEUEID > LEFT JOIN RiskDefinition ON ChangeDetails.RISKID=RiskDefinition.RISKID > LEFT JOIN StageDefinition ON ChangeDetails.STAGEID=StageDefinition.STAGEID > LEFT JOIN SubCategoryDefinition ON ChangeDetails.SUBCATEGORYID= > SubCategoryDefinition.SUBCATEGORYID LEFT JOIN UrgencyDefinition ON > ChangeDetails.URGENCYID=UrgencyDefinition.URGENCYID LEFT JOIN SDUser ON > ChangeDetails.INITIATORID=SDUser.USERID; > LOG: statement: SELECT COUNT(*) FROM ChangeDetails LEFT JOIN > SDOrganization AaaOrg ON ChangeDetails.SITEID=AaaOrg.ORG_ID LEFT JOIN > ApprovalStatusDefinition ON ChangeDetails.APPR_STATUSID= > ApprovalStatusDefinition.STATUSID LEFT JOIN CategoryDefinition ON > ChangeDetails.CATEGORYID=CategoryDefinition.CATEGORYID LEFT JOIN > Change_Fields ON ChangeDetails.CHANGEID=Change_Fields.CHANGEID LEFT JOIN > Change_StageDefinition ON > ChangeDetails.WFSTAGEID=Change_StageDefinition.WFSTAGEID > LEFT JOIN Change_StatusDefinition ON ChangeDetails.WFSTATUSID= > Change_StatusDefinition.WFSTATUSID LEFT JOIN AaaUser ChangeManager ON > ChangeDetails.CHANGEMANAGERID=ChangeManager.USER_ID LEFT JOIN AaaUser > ChangeOriginator ON ChangeDetails.INITIATORID=ChangeOriginator.USER_ID > LEFT JOIN AaaUser ChangeOwner ON > ChangeDetails.TECHNICIANID=ChangeOwner.USER_ID > LEFT JOIN ChangeResolution ON ChangeDetails.CHANGEID=ChangeResolution.CHANGEID > LEFT JOIN ChangeTemplate ON ChangeDetails.TEMPLATEID=ChangeTemplate.TEMPLATEID > LEFT JOIN ChangeToClosureCode ON > ChangeDetails.CHANGEID=ChangeToClosureCode.CHANGEID > LEFT JOIN Change_ClosureCode ON ChangeToClosureCode.ID=Change_ClosureCode.ID > LEFT JOIN ChangeTypeDefinition ON ChangeDetails.CHANGETYPEID= > ChangeTypeDefinition.CHANGETYPEID LEFT JOIN ChangeWF_Definition ON > ChangeDetails.WFID=ChangeWF_Definition.ID LEFT JOIN ImpactDefinition ON > ChangeDetails.IMPACTID=ImpactDefinition.IMPACTID LEFT JOIN ItemDefinition > ON ChangeDetails.ITEMID=ItemDefinition.ITEMID LEFT JOIN > PriorityDefinition ON ChangeDetails.PRIORITYID=PriorityDefinition.PRIORITYID > LEFT JOIN QueueDefinition ON ChangeDetails.GROUPID=QueueDefinition.QUEUEID > LEFT
Re: Query is slow when run for first time; subsequent execution is fast
Hello Jeff, Thanks for the insights. >Don't keep closing and reopening connections. Even if I close a connection and open a new one and execute the same query, the planning time is considerably less than the first time. Only when I restart the Postgres server then I face high planning time again. >The query plan itself is not cached, but all the metadata about the (large number) of tables used in the query is cached. Apparently reading/parsing that data is the slow step, not coming up with the actual plan. I enabled logging for parser, planner etc in postgresql.conf and re run the queries. Following is the logs - I am not sure exactly how this should be read, but the major difference in elapsed time seems to be in PLANNER STATISTICS section. -- start -- 1. First run LOG: PARSER STATISTICS DETAIL: ! system usage stats: ! 0.000482 elapsed 0.000356 user 0.000127 system sec ! [0.004921 user 0.004824 sys total] ! 0/0 [0/1] filesystem blocks in/out ! 0/102 [0/1076] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [3/5] messages rcvd/sent ! 0/0 [8/11] voluntary/involuntary context switches STATEMENT: SELECT COUNT(*) FROM ChangeDetails LEFT JOIN SDOrganization AaaOrg ON ChangeDetails.SITEID=AaaOrg.ORG_ID LEFT JOIN ApprovalStatusDefinition ON ChangeDetails.APPR_STATUSID=ApprovalStatusDefinition.STATUSID LEFT JOIN CategoryDefinition ON ChangeDetails.CATEGORYID=CategoryDefinition.CATEGORYID LEFT JOIN Change_Fields ON ChangeDetails.CHANGEID=Change_Fields.CHANGEID LEFT JOIN Change_StageDefinition ON ChangeDetails.WFSTAGEID=Change_StageDefinition.WFSTAGEID LEFT JOIN Change_StatusDefinition ON ChangeDetails.WFSTATUSID=Change_StatusDefinition.WFSTATUSID LEFT JOIN AaaUser ChangeManager ON ChangeDetails.CHANGEMANAGERID=ChangeManager.USER_ID LEFT JOIN AaaUser ChangeOriginator ON ChangeDetails.INITIATORID=ChangeOriginator.USER_ID LEFT JOIN AaaUser ChangeOwner ON ChangeDetails.TECHNICIANID=ChangeOwner.USER_ID LEFT JOIN ChangeResolution ON ChangeDetails.CHANGEID=ChangeResolution.CHANGEID LEFT JOIN ChangeTemplate ON ChangeDetails.TEMPLATEID=ChangeTemplate.TEMPLATEID LEFT JOIN ChangeToClosureCode ON ChangeDetails.CHANGEID=ChangeToClosureCode.CHANGEID LEFT JOIN Change_ClosureCode ON ChangeToClosureCode.ID=Change_ClosureCode.ID LEFT JOIN ChangeTypeDefinition ON ChangeDetails.CHANGETYPEID=ChangeTypeDefinition.CHANGETYPEID LEFT JOIN ChangeWF_Definition ON ChangeDetails.WFID=ChangeWF_Definition.ID LEFT JOIN ImpactDefinition ON ChangeDetails.IMPACTID=ImpactDefinition.IMPACTID LEFT JOIN ItemDefinition ON ChangeDetails.ITEMID=ItemDefinition.ITEMID LEFT JOIN PriorityDefinition ON ChangeDetails.PRIORITYID=PriorityDefinition.PRIORITYID LEFT JOIN QueueDefinition ON ChangeDetails.GROUPID=QueueDefinition.QUEUEID LEFT JOIN RiskDefinition ON ChangeDetails.RISKID=RiskDefinition.RISKID LEFT JOIN StageDefinition ON ChangeDetails.STAGEID=StageDefinition.STAGEID LEFT JOIN SubCategoryDefinition ON ChangeDetails.SUBCATEGORYID=SubCategoryDefinition.SUBCATEGORYID LEFT JOIN UrgencyDefinition ON ChangeDetails.URGENCYID=UrgencyDefinition.URGENCYID LEFT JOIN SDUser ON ChangeDetails.INITIATORID=SDUser.USERID; LOG: statement: SELECT COUNT(*) FROM ChangeDetails LEFT JOIN SDOrganization AaaOrg ON ChangeDetails.SITEID=AaaOrg.ORG_ID LEFT JOIN ApprovalStatusDefinition ON ChangeDetails.APPR_STATUSID=ApprovalStatusDefinition.STATUSID LEFT JOIN CategoryDefinition ON ChangeDetails.CATEGORYID=CategoryDefinition.CATEGORYID LEFT JOIN Change_Fields ON ChangeDetails.CHANGEID=Change_Fields.CHANGEID LEFT JOIN Change_StageDefinition ON ChangeDetails.WFSTAGEID=Change_StageDefinition.WFSTAGEID LEFT JOIN Change_StatusDefinition ON ChangeDetails.WFSTATUSID=Change_StatusDefinition.WFSTATUSID LEFT JOIN AaaUser ChangeManager ON ChangeDetails.CHANGEMANAGERID=ChangeManager.USER_ID LEFT JOIN AaaUser ChangeOriginator ON ChangeDetails.INITIATORID=ChangeOriginator.USER_ID LEFT JOIN AaaUser ChangeOwner ON ChangeDetails.TECHNICIANID=ChangeOwner.USER_ID LEFT JOIN ChangeResolution ON ChangeDetails.CHANGEID=ChangeResolution.CHANGEID LEFT JOIN ChangeTemplate ON ChangeDetails.TEMPLATEID=ChangeTemplate.TEMPLATEID LEFT JOIN ChangeToClosureCode ON ChangeDetails.CHANGEID=ChangeToClosureCode.CHANGEID LEFT JOIN Change_ClosureCode ON ChangeToClosureCode.ID=Change_ClosureCode.ID LEFT JOIN ChangeTypeDefinition ON ChangeDetails.CHANGETYPEID=ChangeTypeDefinition.CHANGETYPEID LEFT JOIN ChangeWF_Definition ON ChangeDetails.WFID=ChangeWF_Definition.ID LEFT JOIN ImpactDefinition ON ChangeDetails.IMPACTID=ImpactDefinition.IMPACTID LEFT JOIN ItemDefinition ON ChangeDetails.ITEMID=ItemDefinition.ITEMID LEFT JOIN PriorityDefinition ON ChangeDetails.PRIORITYID=PriorityDefinition.PRIORITYID LEFT JOIN QueueDefinition ON ChangeDetails.GROUPID=QueueDefinition.QUEUEID LEFT JOIN RiskDefinition ON ChangeDetails.RISKID=RiskDefinition.RISKID LEFT JOIN StageDefinition ON ChangeDetails.STAGEID=StageDefinition.STAGEID LEFT JOIN SubCategoryDefinition ON ChangeDetails.SUBCATEGORYID=SubCategoryDefinition.SU
Re: Query is slow when run for first time; subsequent execution is fast
On Wed, Jan 10, 2018 at 3:59 AM, Nandakumar M wrote: > > I am not using prepared statements. Postgres documentation and previous > questions in the pgsql-performance mailing list mention that the query plan > is cached only when prepared statements are used. > > https://www.postgresql.org/message-id/15600.1346885470%40sss.pgh.pa.us > > In the above thread Tom Lane mentions that the plan is never cached for > raw queries. Yet, this is exactly what seems to be happening in my case. Am > I missing something? > The query plan itself is not cached, but all the metadata about the (large number) of tables used in the query is cached. Apparently reading/parsing that data is the slow step, not coming up with the actual plan. > Please let me know how I can make sure the query execution for the first time is fast too. Don't keep closing and reopening connections. Use a connection pooler (pgbouncer, pgpool, whatever pooler is built into your language/library/driver, etc.) if necessary to accomplish this. Cheers, Jeff
