CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Emme Watson
Emme Watson

Reputation: 21

Measure query execution time excluding start-up cost in postgres

I want to measure the total time taken by postgres to execute my query excluding the start-up cost. Earlier I was using \timing but now I found \timing includes start-up cost.

I also tried: "explain analyze" in which I found that actual time is specified in a particular format like: actual time=12.04..12.09 So, does this mean that the time taken to execute postgres query excluding start-up time is 0.05. If not, then is there a way to exclude start-up costs and measure query execution time?

Upvotes: 2

Views: 1099

Answers (2)

Alex
Alex

Reputation: 1206

For exploring PLANNING costs and EXECUTE costs separately you need to set on several postgres.conf parameters:

log_planner_stats = on
log_executor_stats = on

and explore your log file.

Update: 1. find your config file location with executing:

SHOW config_file;

2. Set parameters. Don't foget to remove comment-symbol '#'. 3. Restart postgresql service 4. Execute your query 5. Explore your log file.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324375

What you want is actually quite ill-defined.

"Startup cost" could mean:

  • network connection overhead and back-end start cost of establishing a new connection. Avoided by re-using the same session.

  • network round-trip times for sending the query and getting the results. Avoided by measuring the timing server-side with log_statement_min_duration = 0 or (with timing overhead) using explain analyze or the auto_explain module.

  • Query planning time. Avoided by PREPAREing the query, then timing only the subsequent EXECUTE.

  • Lock acquisition time. There is not currently any way to exclude this.

Note that using EXPLAIN ANALYZE may not be ideal for your purposes: it throws the query result away, and it adds its own costs because of the detailed timing it does. I would set log_statement_min_duration = 0, set client_min_messages appropriately, and capture the timings from the log output.

So it sounds like you want to PREPARE a query then EXPLAIN ANALYZE EXECUTE it or just EXECUTE it with log_statement_min_duration set to 0.

Upvotes: 3

Related Questions