From: | Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> |
---|---|
To: | Haider Ali <alihaider907(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Understanding PostgreSQL query execution time |
Date: | 2017-04-07 15:15:37 |
Message-ID: | 176827072.466738.1491578136971.JavaMail.root@fmed.uba.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
----- Mensaje original -----
> De: "Haider Ali" <alihaider907(at)gmail(dot)com>
> Para: pgsql-performance(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dinesh Chandra 12108 | 2017-04-07 15:18:58 | Re: Filter certain range of IP address. |
Previous Message | ldh@laurent-hasson.com | 2017-04-07 15:03:18 | Re: Understanding PostgreSQL query execution time |