How to get timing from the PgSQL DB

user_2801435 0
Viewed 203

I'm attempting to get the timing of a query using the pg gem. I saw the documentation, but I did not find anything. I want to know if there is something like query.time.

I added \timing to ~/.psqlrc, so the command is active by default. If I write the query in the Postgres console the time is active.

This is the code:

conn = PGconn.open(:dbname => 'my_db') 
query=conn.exec('SELECT * from some_table')
puts query.num_tuples -> this work
puts query.time  -> undefined method

I need to measure the time of Postgres itself, I cannot use the Time class from Ruby.

pg timing postgresql ruby
2 Answers

Although PGconn.open(:dbname => 'my_db') and conn.exec appears to work, it does not correctly report execution time. I suspect it reports the execution time for something besides the query. Perhaps it is the execution time of the EXPLAIN.

Although the question does not specify Rails, I suspect that using PGconn without Rails would have the same issue.

PGconn.open(:dbname => 'my_db') always reports the execution time between 0.010 and 0.285 ms. The correct execution time for my query was about 15,000ms.

To get the correct execution time use:

ActiveRecord::Base.connection.execute("EXPLAIN ANALYZE ...").values.last.first

To extract the numeric value:

ActiveRecord::Base.connection.execute("EXPLAIN ANALYZE ...").values.last.first.scan( /\d+.\d+/ ).first

In terms of the OP,

conn = ActiveRecord::Base.connection
conn.execute('EXPLAIN ANALYZE SELECT * from some_table')
user_3369200
Related