Обсуждение: [PERFORM] Understanding PostgreSQL query execution time

----- 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
aram cache. 

HTH
Gerardo
>
> Haider Ali

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.