Re: Query is slow when run for first time; subsequent execution is fast

[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 JOIN
RiskDefinition ON ChangeDetails.RISKID=RiskDefinition.RISKID LEFT JOIN
StageDefinition ON ChangeDetails.STAGEID=StageDefinition.STAGEID LEFT JOIN
SubCategoryDefinition ON
ChangeDetails.SUBCATEGORYID=SubCategoryDefinition.SU

RE: Slow queries after Windows startup

[Éric Fontaine]

You should check this blog:
http://blog.coelho.net/database/2013/08/14/postgresql-warmup.html
To warm-up your DB after reboot.
Let me know 
Regards
Eric



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: PGadmin error while connecting with database.

[Mariel Cherkassky]

Do you any errors in the server log  or in the log of pgadmin (show us..)?
What are the settings that you configured for your pgadmin client  and for
the connection ?

2018-01-12 6:55 GMT+02:00 Dinesh Chandra 12108 :

> Dear Expert,
>
>
>
> While connecting PostgreSQL 9.3 with PGAdmin client I am getting the below
> error.
>
>
>
>
>
> However I am able to connect the database using psql  thourgh Putty.
>
>
>
> *Entry in pg_hba.conf*
>
>
>
> # IPv4 local connections:
>
> hostall all 127.0.0.1/32   md5
>
> hostall all 0.0.0.0/0  md5
>
>
>
> *entry in Postgresql.conf*
>
>
>
> listen_addresses = '*'
>
> port = 5432
>
>
>
> port is already open and I am able to telnet.
>
>
>
> Even after creating a new instance and fresh installation of PostgreSQL, I
> am getting the same error.
>
>
>
> OS-CentOS 7
>
> S/w-PostgreSQL9.3
>
>
>
> *Regards,*
>
> *Dinesh Chandra*
>
> *|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.*
>
> *--*
>
> Mobile: +91-9953975849 | Ext 1078 |dinesh.chan...@cyient.com
>
> Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India
> 
> .
>
>
>
> --
>
> DISCLAIMER:
>
> This email message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not the
> intended recipient, please contact the sender by reply email and destroy
> all copies of the original message. Check all attachments for viruses
> before opening them. All views or opinions presented in this e-mail are
> those of the author and may not reflect the opinion of Cyient or those of
> our affiliates.
>

Re: Query is slow when run for first time; subsequent execution is fast

[Pavel Stehule]

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