pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Understanding PostgreSQL query execution time 4+ messages / 4 participants [nested] [flat]
* Understanding PostgreSQL query execution time @ 2017-04-07 14:56 Haider Ali <alihaider907@gmail.com> 0 siblings, 3 replies; 4+ messages in thread From: Haider Ali @ 2017-04-07 14:56 UTC (permalink / raw) To: pgsql-performance Hello I want to understand execution time of a query in PostgreSQL then I want to relate it to the problem i am getting. According to my observation ( I can't explain why this happen ) whenever we query a table first time its execution will be high (sometimes very high) as compare to queries made on same table in a short period of time followed by first query on that table. For example query given below *SELECT "global_configs".* FROM "global_configs" ORDER BY "global_configs"."id" ASC LIMIT $1* executed multiple times instantaneous one after another have following execution time 1st time => *147.5ms* *2nd time => 3.0ms* *3rd time => 3.0ms* *4th time => 3.0ms* *5th time => 0.8ms* I want to understand why there is a huge time difference between 1st and rest of the executions. *Relation to other problem* Having experience above behaviour of PostgreSQL now I am using PostgreSQL managed by Amazon RDS. Observation is no matter how many times I execute same query its execution times remain same ( although execution time of a query on RDS is comparatively high as compare to query running on local instance of PostgreSQL that I can understand is because of Network latency) *Questions* 1. Why first query on a table takes more time then queries followed by it ? 2. Why above behaviour doesn't reflect on Amazon RDS ? Thank you for reading my post. -- Haider Ali ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Understanding PostgreSQL query execution time @ 2017-04-07 15:03 ldh@laurent-hasson.com <ldh@laurent-hasson.com> parent: Haider Ali <alihaider907@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: ldh@laurent-hasson.com @ 2017-04-07 15:03 UTC (permalink / raw) To: Haider Ali <alihaider907@gmail.com>; pgsql-performance The first behavior is very likely just caching. The plan and results from the query are cached, so the second time, it's reused directly. If you ran a bunch of other queries in the middle and effectively exhausted the cache, then back to your query, likely tou'd see the 'slow' behavior again. As for AWS, not sure, but likely about memory and config more than latency. Sent from my BlackBerry 10 smartphone. From: Haider Ali Sent: Friday, April 7, 2017 09:58 To: pgsql-performance@postgresql.org Subject: [PERFORM] Understanding PostgreSQL query execution time Hello I want to understand execution time of a query in PostgreSQL then I want to relate it to the problem i am getting. According to my observation ( I can't explain why this happen ) whenever we query a table first time its execution will be high (sometimes very high) as compare to queries made on same table in a short period of time followed by first query on that table. For example query given below SELECT "global_configs".* FROM "global_configs" ORDER BY "global_configs"."id" ASC LIMIT $1 executed multiple times instantaneous one after another have following execution time 1st time => 147.5ms 2nd time => 3.0ms 3rd time => 3.0ms 4th time => 3.0ms 5th time => 0.8ms I want to understand why there is a huge time difference between 1st and rest of the executions. Relation to other problem Having experience above behaviour of PostgreSQL now I am using PostgreSQL managed by Amazon RDS. Observation is no matter how many times I execute same query its execution times remain same ( although execution time of a query on RDS is comparatively high as compare to query running on local instance of PostgreSQL that I can understand is because of Network latency) Questions 1. Why first query on a table takes more time then queries followed by it ? 2. Why above behaviour doesn't reflect on Amazon RDS ? Thank you for reading my post. -- Haider Ali ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Understanding PostgreSQL query execution time @ 2017-04-07 15:15 Gerardo Herzig <gherzig@fmed.uba.ar> parent: Haider Ali <alihaider907@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: Gerardo Herzig @ 2017-04-07 15:15 UTC (permalink / raw) To: Haider Ali <alihaider907@gmail.com>; +Cc: pgsql-performance ----- Mensaje original ----- > De: "Haider Ali" <alihaider907@gmail.com> > Para: pgsql-performance@postgresql.org > Enviados: Viernes, 7 de Abril 2017 11:56:53 > Asunto: [PERFORM] Understanding PostgreSQL query execution time > > > Hello > > > I want to understand execution time of a query in PostgreSQL then I > want to relate it to the problem i am getting. According to my > observation ( I can't explain why this happen ) whenever we query a > table first time its execution will be high (sometimes very high) as > compare to queries made on same table in a short period of time > followed by first query on that table. For example query given below > > > SELECT "global_configs".* FROM "global_configs" ORDER BY > "global_configs"."id" ASC LIMIT $1 > > > > > executed multiple times instantaneous one after another have > following execution time > > > 1st time => 147.5ms > > 2nd time => 3.0ms > > 3rd time => 3.0ms > > 4th time => 3.0ms > > 5th time => 0.8ms That is the effects of the postgres/Linux cache for shure. > > > I want to understand why there is a huge time difference between 1st > and rest of the executions. > > > Relation to other problem > > > Having experience above behaviour of PostgreSQL now I am using > PostgreSQL managed by Amazon RDS. Observation is no matter how many > times I execute same query its execution times remain same ( > although execution time of a query on RDS is comparatively high as > compare to query running on local instance of PostgreSQL that I can > understand is because of Network latency) > > > Questions > > > > > 1. Why first query on a table takes more time then queries > followed by it ? > 2. Why above behaviour doesn't reflect on Amazon RDS ? > Amazon provides you with SSD like disks, running close to memory speed. That would explain the little impact of having a ram cache. HTH Gerardo > > Haider Ali -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Understanding PostgreSQL query execution time @ 2017-04-10 12:18 vinny <vinny@xs4all.nl> parent: Haider Ali <alihaider907@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: vinny @ 2017-04-10 12:18 UTC (permalink / raw) To: Haider Ali <alihaider907@gmail.com>; +Cc: pgsql-performance On 2017-04-07 16:56, Haider Ali wrote: > Hello > > I want to understand execution time of a query in PostgreSQL then I > want to relate it to the problem i am getting. According to my > observation ( I can't explain why this happen ) whenever we query a > table first time its execution will be high (sometimes very high) as > compare to queries made on same table in a short period of time > followed by first query on that table. For example query given below The first time a query is executed it is quite likely that the data it needs is not in RAM yet, so it must fetch the data from disk, which is slow. But, benchmarking is an art; did you execute these queries separately from the commandline? Otherwise where may be other forces at work here... > > Having experience above behaviour of PostgreSQL now I am using > PostgreSQL managed by Amazon RDS. Observation is no matter how many > times I execute same query its execution times remain same ( although > execution time of a query on RDS is comparatively high as compare to > query running on local instance of PostgreSQL that I can understand is > because of Network latency) The problem may go away entirely if the database/OS has enough RAM available, and configured, for caching. The problem on your local system may be simply a case of PostgreSQL or the OS removing tuples/index data from RAM when it feels it can make better use of that RAM space for other things if you don't access that data for a while. Try spying on your system with iotop and such tools to see what the server is actually doing during the first query. If there is a spike in disk-IO then you've found the cause; the tuples where not in RAM. You may also want to run an EXPLAIN to make sure that the fast queries are not purely the result of some query-result cache. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-04-10 12:18 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-04-07 14:56 Understanding PostgreSQL query execution time Haider Ali <alihaider907@gmail.com> 2017-04-07 15:03 ` ldh@laurent-hasson.com <ldh@laurent-hasson.com> 2017-04-07 15:15 ` Gerardo Herzig <gherzig@fmed.uba.ar> 2017-04-10 12:18 ` vinny <vinny@xs4all.nl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox