How to measure function performance ("now()" always gives me the same value)?

Question

Right now I am recording the start/stop times inside my functions using NOW() and storing them in a separate table as TIMESTAMPs. The whole process is messy, but the real problem is that the start and stop times are identical even though some of the queries are quite complex which leads me to believe that no matter how many times you call NOW() from inside a function, it will return the same value. I tried writing to a double Precision value but I am still getting a duration of 0.

How would I go about accurately getting the duration of a Postgres function?

user_330315 · Answer

As documented in the manual now() (and it's standard SQL equivalent current_timestamp) return the time at the start of the transaction.

If you want to get the "real" current time, use clock_timestamp().

user_406691 · Answer

A @a_horse_with_no_name is absolutely correct - but for performance measuring you can use some tools:

build-in function execution statistics - see pg_stat_user_functions
an extension plpgsql_check has integrated profiler enter link description here

How to measure function performance ("now()" always gives me the same value)?

2 Answers