Re: pg_stat_statements: calls under-estimation propagation - Mailing list pgsql-hackers

On Tue, Oct 1, 2013 at 12:48 AM, Daniel Farina-5 [via PostgreSQL] <br /><<a
href="/user/SendEmail.jtp?type=node&node=5772955&i=0"link="external" rel="nofollow" target="_top">[hidden
email]</a>>wrote: <div class="shrinkable-quote"><br />> <br />> On Sep 30, 2013 4:39 AM, "Sameer Thakur"
<[hiddenemail]> wrote: <br />>> <br />>> > Also, for onlookers, I have changed this patch around
todo the <br />>> > date-oriented stuff but want to look it over before stapling it up and <br />>> >
sendingit.  If one cannot wait, one can look at <br />>> > <a
href="https://github.com/fdr/postgres/tree/queryid"link="external" rel="nofollow"
target="_top">https://github.com/fdr/postgres/tree/queryid</a>. The squashed-version of <br />>> > that
historycontains a reasonable patch I think, but a re-read often <br />>> > finds something for me and I've
onlyjust completed it yesterday. <br />>> > <br />>> <br />>> I did the following <br />>>
1.Forked from fdr/postgres <br />>> 2. cloned branch queryid <br />>> 3. squashed <br />>>
22899c802571a57cfaf0df38e6c5c366b5430c74<br />>> d813096e29049667151a49fc5e5cf3d6bbe55702 <br />>> picked
<br/>>> be2671a4a6aa355c5e8ae646210e6c8e0b84ecb5 <br />>> 4. usual make/make install/create extension
pg_stat_statements.<br />>> (pg_stat_statements.max=100). <br />>> 5. select * from
pg_stat_statements_reset(),select * from <br />>> pgbench_tellers. <br />>> result below: <br />>>
<br/>>> userid | dbid  |          session_start           |        introduced <br />>>        |            
     query                   |      query_id <br />>>   | calls | total_time | <br />>>  rows |
shared_blks_hit| shared_blks_read | shared_blks_dirtied | <br />>> shared_blks_written | local_blks_hit |
local_blks_read| <br />>> local_blks_dirtied | local_blks_written | t <br />>> emp_blks_read |
temp_blks_written| blk_read_time | blk_write_time <br />>> <br />>>
--------+-------+----------------------------------+---------------------------+-------------------------------------------+---------------------+-------+------------+
<br/>>> <br />>>
------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+--
<br/>>> --------------+-------------------+---------------+---------------- <br />>>      10 | 12900 |
2013-09-3016:55:22.285113+05:30 | 1970-01-01 <br />>> 05:30:00+05:30 | select * from pg_stat_statements_reset();
|<br />>> 2531907647060518039 |     1 |          0 | <br />>>     1 |               0 |                0 |
                 0 | <br />>>               0 |              0 |               0 | <br />>> 0 |            
    0 | <br />>>             0 |                 0 |             0 |              0 <br />>>      10 |
12900| 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 <br />>> 05:30:00+05:30 | select * from pgbench_tellers ;  
       | <br />>> 7580333025384382649 |     1 |          0 | <br />>>    10 |               1 |            
  0 |                   0 | <br />>>               0 |              0 |               0 | <br />>> 0 |    
            0 | <br />>>             0 |                 0 |             0 |              0 <br />>> (2
rows)<br />>> <br />>> <br />>> I understand session_start and verified that it changes with each <br
/>>>database restart to reflect current time. <br />> <br />> It should only restart when the statistics
filecannot be loaded. </div><br />This seems to work fine. <br />1. Started the instance <br />2. Executed
pg_stat_statements_reset(),select * from <br />pgbench_history,select* from pgbench_tellers. Got the following in <br
/>pg_stat_statementsview <br />userid | dbid  |          session_start           | <br />introduced            |      
           query                   | <br />   query_id       | calls | tota <br />l_time | rows | shared_blks_hit |
shared_blks_read| <br />shared_blks_dirtied | shared_blks_written | local_blks_hit | <br />local_blks_read |
local_blks_dirtied| local_blks_wri <br />tten | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time <br
/>--------+-------+----------------------------------+----------------------------------+-------------------------------------------+----------------------+-------+-----
<br
/>-------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+---------------
<br/>-----+----------------+-------------------+---------------+---------------- <br />     10 | 12900 | 2013-10-01
17:43:26.667074+05:30| 2013-10-01 <br />17:43:43.724301+05:30 | select * from pgbench_history;            | <br
/>-165801328395488047|     1 | <br />     0 |    0 |               0 |                0 | <br />0 |                   0
|             0 |               0 | <br />       0 | <br />   0 |              0 |                 0 |             0 |
            0 <br />     10 | 12900 | 2013-10-01 17:43:26.667074+05:30 | 2013-10-01 <br />17:43:37.379785+05:30 |
select* from pgbench_tellers;            | <br />8376871363863945311 |     1 | <br />     0 |   10 |               0 |
              1 | <br />0 |                   0 |              0 |               0 | <br />       0 | <br />   0 |    
        0 |                 0 |             0 |              0 <br />     10 | 12900 | 2013-10-01 17:43:26.667074+05:30
|2013-10-01 <br />17:43:26.667178+05:30 | select * from pg_stat_statements_reset(); | <br />-1061018443194138344 |    
1| <br />     0 |    1 |               0 |                0 | <br />0 |                   0 |              0 |        
     0 | <br />       0 | <br />   0 |              0 |                 0 |             0 |              0 <br />(3
rows)<br /><br />Then restarted the server and saw pg_stat_statements view again. <br /><br />userid | dbid  |        
 session_start          | <br />introduced            |                   query                   | <br />   query_id  
   | calls | tota <br />l_time | rows | shared_blks_hit | shared_blks_read | <br />shared_blks_dirtied |
shared_blks_written| local_blks_hit | <br />local_blks_read | local_blks_dirtied | local_blks_wri <br />tten |
temp_blks_read| temp_blks_written | blk_read_time | blk_write_time <br
/>--------+-------+----------------------------------+----------------------------------+-------------------------------------------+----------------------+-------+-----
<br
/>-------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+---------------
<br/>-----+----------------+-------------------+---------------+---------------- <br />     10 | 12900 | 2013-10-01
17:43:26.667074+05:30| 2013-10-01 <br />17:45:15.130261+05:30 | select * from pgbench_history;            | <br
/>-165801328395488047|     1 | <br />     0 |    0 |               0 |                0 | <br />0 |                   0
|             0 |               0 | <br />       0 | <br />   0 |              0 |                 0 |             0 |
            0 <br />     10 | 12900 | 2013-10-01 17:43:26.667074+05:30 | 2013-10-01 <br />17:45:15.130266+05:30 |
select* from pg_stat_statements ;        | <br />-247576122750898541 |     1 | <br />     0 |    3 |               0 |
              0 | <br />0 |                   0 |              0 |               0 | <br />       0 | <br />   0 |    
        0 |                 0 |             0 |              0 <br />     10 | 12900 | 2013-10-01 17:43:26.667074+05:30
|2013-10-01 <br />17:45:15.130271+05:30 | select * from pgbench_tellers;            | <br />8376871363863945311 |     1
|<br />     0 |   10 |               0 |                1 | <br />0 |                   0 |              0 |          
   0 | <br />       0 | <br />   0 |              0 |                 0 |             0 |              0 <br />     10
|12900 | 2013-10-01 17:43:26.667074+05:30 | 2013-10-01 <br />17:45:15.130276+05:30 | select * from
pg_stat_statements_reset();| <br />-1061018443194138344 |     1 | <br />     0 |    1 |               0 |              
 0| <br />0 |                   0 |              0 |               0 | <br />       0 | <br />   0 |              0 |  
             0 |             0 |              0 <br />(4 rows) <br /><br />Correctly, session start remains same after
restartfor all queries <br />and introduced time differs slightly reflecting re-introduction of <br />statistics into
hashtableafter reading from statistics file. Also, <br />correctly, queryid remains same for all queries. <br /><br
/>Nowshutdown and delete pg_stat_statements.stat under data/global. <br />Restart again and check pg_stat_statements
view.<br /><br /> userid | dbid | session_start | introduced | query | query_id | calls <br />| total_time | rows |
shared_blks_hit| shared_blks_read | <br />shared_blks_dirtied | shared_blks_wri <br />tten | local_blks_hit |
local_blks_read| local_blks_dirtied | <br />local_blks_written | temp_blks_read | temp_blks_written | <br
/>blk_read_time| blk_write_time <br
/>--------+------+---------------+------------+-------+----------+-------+------------+------+-----------------+------------------+---------------------+----------------
<br
/>-----+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
<br/>(0 rows) <br /><br />Correctly it has been reset. <br /><br />regards <br />Sameer <br /><br /><hr align="left"
width="300"/> View this message in context: <a
href="http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5772955.html">Re:
pg_stat_statements:calls under-estimation propagation</a><br /> Sent from the <a
href="http://postgresql.1045698.n5.nabble.com/PostgreSQL-hackers-f1928748.html">PostgreSQL- hackers mailing list
archive</a>at Nabble.com.<br />