Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I configured my postgresql.conf (my version is 9.1) with the normal persistent configuration:

shared_preload_libraries = 'pg_stat_statements,plperl'
custom_variable_classes = 'pg_stat_statements,plperl'
pg_stat_statements.max = 100
pg_stat_statements.track = all
pg_stat_statements.save = on

I let one month to run within a database which is constantly accessed with read/write queries.

After a month (or generally enough time to be sure lots of queries were executed) I do the documentation query with top five slowest queries, and get this result:

-[ RECORD 1 ]---------------------------------------------------------------
query       | UPDATE XXXXXXXX
calls       | 63486
total_time  | 238.271792999997
rows        | 40740
hit_percent | 99.9938060778852878
-[ RECORD 2 ]---------------------------------------------------------------
query       | SELECT XXXXXXXX
calls       | 67347
total_time  | 0.852444000000713
rows        | 67347
hit_percent | 100.0000000000000

... and so on. I see the real number of calls as total_time is (very) much bigger for this queries than this view says. I don't understand how this works, because I expected hours in the total_time field as my server is running for more than 3 months without restarting.

In general is a no-change-from-doc configuration, and I get nonsense results.

share|improve this question
1  
The total time is the elapsed query time for the given query, not the time the database was up for. Thought it was obvious... –  FreshPhilOfSO Feb 4 '13 at 0:00
add comment

1 Answer

I quote the manual (at the page you are linking to, just for the appropriate version 9.1)

total_time ... Total time spent in the statement, in seconds

67347 calls totaling 0.852 seconds. That's 0.012 ms per call.
Not uncommon for a simple SELECT.

If that cannot explain it, consider this quote from the manual:

Note that statements are considered the same if they have the same text, regardless of the values of any out-of-line parameters used in the statement. Using out-of-line parameters will help to group statements together and may make the statistics more useful.

We'd have to study your exact statement(s) to assess that.

There is also the function pg_stat_statements_reset() to reset statistics (superusers only). Are you sure it wasn't called?

And there are a number of configuration parameters to regulate what is tracked. Any fancy settings there?

share|improve this answer
    
Its supposed to be the 2nd slower query on my box, and as I said, the server is running for 3 month, it's imposible that the 2nd query had only spent 0.82 seconds in 3 months. –  avances123 Feb 12 '13 at 1:49
    
I added a bit more to my answer. It's mostly guesswork, with the information we have here. –  Erwin Brandstetter Feb 12 '13 at 8:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.