Date: Fri, 7 Apr 2017 12:15:37 -0300 (ART)
From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Haider Ali <alihaider907@gmail.com>
Cc: pgsql-performance@postgresql.org
Message-ID: <176827072.466738.1491578136971.JavaMail.root@fmed.uba.ar>
In-Reply-To: 
 <CAMDfpCTxRFE_apC1-2PqRW9uXTtoP7XzN9wqoLbNgdGKUn5_DA@mail.gmail.com>
Subject: Re: Understanding PostgreSQL query execution time
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


----- 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