I want to benchmark performance of a particular complicated query JOIN for various values of primary key ID. Most previous approaches say to use EXPLAIN ANALYZE for the query in question and get the "Total runtime: .... ms" in order to count performance. The problem is, that even when I use the same ID, I get very different results, e.g. time ranging from 20sec - 140sec. Note that cache has nothing to do with that, since when running the same query at different hours I get this kind of variation, even when we are talking about the same ID (I always get the same query plan).
So my questions are:
- Does Total runtime depends on the client / interface (e.g. pgAdminIII vs psql). I think that psql gives more consistent values
- Does it depend on network time (should I run on localhost or I can run even in a remote machine)
- Is there a way to take more consistent measurements or this kind of fluctuations are condidered normal?
I am using PostgreSQL 9.2.6 64bit on a linux Ubuntu 12.10 64bit machine with 2GB of shared buffers.