Re: survey: pg_stat_statements total_time and entry deallocation

From: Kim Rose Carlsen <krc(at)hiper(dot)dk>
To: Jeremy Schneider <schnjere(at)amazon(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: survey: pg_stat_statements total_time and entry deallocation
Date: 2018-09-11 07:56:53
Message-ID: VI1PR0502MB364624E14E93BE0909B6ED02C7040@VI1PR0502MB3646.eurprd05.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Here is some data from our production. I hope it can be of any use to you.

----- System 1:
hiper=> select                                  
   bucket
  ,count(*) entries
  ,max(calls) max_calls
  ,round(sum(total_time)) total_time
  ,round((100*sum(total_time)/avg(total_total_time))::numeric,2) pct_time
  ,round(sum(rows)) "rows"
  ,round((100*sum(rows)/avg(total_rows))::numeric,2) pct_rows
from
  (
    select
       ntile(20) over (order by calls) bucket
      ,calls
      ,total_time
      ,sum(total_time) over () total_total_time
      ,rows
      ,sum(rows) over () total_rows
    from pg_stat_statements
  ) stmts
group by rollup(bucket) order by bucket;
 bucket | entries | max_calls | total_time | pct_time |    rows    | pct_rows
--------+---------+-----------+------------+----------+------------+----------
      1 |     245 |        71 |    4745479 |     0.38 |     640677 |     0.04
      2 |     245 |        96 |   22151762 |     1.76 |     236827 |     0.01
      3 |     245 |       122 |    9028387 |     0.72 |     297861 |     0.02
      4 |     244 |       167 |    4711705 |     0.38 |     328928 |     0.02
      5 |     244 |       228 |    9490670 |     0.76 |     337712 |     0.02
      6 |     244 |       305 |    7296024 |     0.58 |     273740 |     0.02
      7 |     244 |       394 |   35828651 |     2.85 |    1140064 |     0.07
      8 |     244 |       540 |   34180388 |     2.72 |    1313171 |     0.08
      9 |     244 |       711 |   29748121 |     2.37 |     865894 |     0.05
     10 |     244 |       989 |   12864432 |     1.02 |    1665529 |     0.10
     11 |     244 |      1507 |    4009346 |     0.32 |    1295032 |     0.08
     12 |     244 |      2511 |   13444734 |     1.07 |    4711699 |     0.30
     13 |     244 |      4567 |  401096681 |    31.94 |    3386595 |     0.21
     14 |     244 |      8086 |    4750899 |     0.38 |    8236002 |     0.52
     15 |     244 |     13356 |   19875345 |     1.58 |    6040996 |     0.38
     16 |     244 |     22454 |   23361859 |     1.86 |   16906926 |     1.06
     17 |     244 |     59660 |   68633113 |     5.46 |   40170089 |     2.52
     18 |     244 |    141667 |   59768727 |     4.76 |   76054887 |     4.77
     19 |     244 |    431946 |  330488976 |    26.31 |  213238961 |    13.38
     20 |     244 | 170978486 |  160486607 |    12.78 | 1216933189 |    76.34
        |    4883 | 170978486 | 1255961906 |   100.00 | 1594074779 |   100.00
(21 rows)

hiper=> select current_setting('pg_stat_statements.max');
 current_setting
-----------------
 5000
(1 row)

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Csaba Ragasits 2018-09-11 08:41:58 Re: PG8.3->10 migration data differences
Previous Message Lutz Gehlen 2018-09-11 07:24:23 Re: timestamp arithmetics in C function