CODEWITHSUNDEEP
rubypostgresqltimingpg
Enzo Zerega
Enzo Zerega

Reputation: 71

How to get timing from the PgSQL DB

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.

Upvotes: 3

Views: 264

Answers (2)

B Seven
B Seven

Reputation: 45941

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')

Upvotes: 1

Nabeel
Nabeel

Reputation: 2302

Try this as it should return the time back.

puts conn.exec("EXPLAIN ANALYZE SELECT * FROM some_table").values

Upvotes: 3

Related Questions