CODEWITHSUNDEEP
postgresql
Pete
Pete

Reputation: 11

How can I bench mark query performance in postgreSQL? CPU or TIME but need to be consistant for every run

How can I bench mark SQL performance in postgreSQL? I tried using Explain Analyze but that gives varied Execution time every time when I repeat same query.

I am applying some tuning techniques on my query and trying to see whether my tuning technique is improving the query performace. The Explain analyze has varying execution times that I cant bechmark and compare . The tuning has imapact in MilliSeconds so I am looking for bechmarch that can give fixed values to compare against.

Upvotes: 1

Views: 173

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246083

There will always be variations in the time it takes a statement to complete:

  • Pages may be cached in memory or have to be read from disk. This is usually the source of the greatest deviations.

  • Concurrent processes may need CPU time

  • You have to wait for internal short time locks (latches) to access a data structure

These are just the first three things that come to my mind.

In short, execution time is always subject to small variations.

Run the query several times and take the the median of the execution times. That is as good as it gets.

Tuning for milliseconds only makes sense if it is a query that is executed a lot.

Also, tuning only makes sense if you have realistic test data. Don't make the mistake to examine and tune a query with only a few test data when it will have to perform with millions of rows.

Upvotes: 1

Related Questions