query execution time (with cache)

From: Neto pr <netopr9(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: query execution time (with cache)
Date: 2018-01-21 18:43:53
Message-ID: CA+wPC0ON51t4A1cmAYykCntf5_Bfq-SYneVgOY-fm3cJfsw_cw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,
I need to know the actual execution time of a query, but considering
that the data is already cached. I also need to make sure that cached
data from other queries is cleared.
I believe that in order to know the real time of a query it will be
necessary to "warm up" the data to be inserted in cache.

Below are the steps suggested by a DBA for me:

Step 1- run ANALYZE on all tables involved before the test;
Step 2- restart the DBMS (to clear the DBMS cache);
Step 3- erase the S.O. cache;
Step 4- execute at least 5 times the same query.

After the actual execution time of the query, it would have to take
the time of the query that is in the "median" among all.

Example:

Execution 1: 07m 58s
Execution 2: 14m 51s
Execution 3: 17m 59s
Execution 4: 17m 55s
Execution 5: 17m 07s

In this case to calculate the median, you must first order each
execution by its time:
Execution 1: 07m 58s
Execution 2: 14m 51s
Execution 5: 17m 07s
Execution 4: 17m 55s
Execution 3: 17m 59s

In this example the median would be execution 5 (17m 07s). Could
someone tell me if this is a good strategy ?
Due to being a scientific work, if anyone has a reference of any
article or book on this subject, it would be very useful.

Best Regards
Neto

Browse pgsql-performance by date

  From Date Subject
Next Message pavan95 2018-01-22 06:09:10 Re: 8.2 Autovacuum BUG ?
Previous Message Magnus Hagander 2018-01-20 13:05:32 Re: pgaudit and create postgis extension logs a lot inserts