-2

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:

  1. Does Total runtime depends on the client / interface (e.g. pgAdminIII vs psql). I think that psql gives more consistent values
  2. Does it depend on network time (should I run on localhost or I can run even in a remote machine)
  3. 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.

  • An explain analyze should not depend on the client. What influences the runtime mostly is other stuff going on the in the database. If you have a lot of active sessions when you run the statement once, and then you have no other activity when you run the statement next, it will give you different runtimes. Also even though you claim cache has nothing to do with it, it sure does. The data that you retrieve now could be aged out of the cache in an hour because other data has been cached. – a_horse_with_no_name Feb 24 '14 at 21:34
  • But I thought the point of EXPLAIN ANALYZE is to get a plan that does not depend on DB activity but only on query specifics. Otherwise it is as good as timing the specific query by count. Or this is for plain EXPLAIN (without the ANALYZE) – Alexandros Feb 24 '14 at 21:38
  • The plan doesn't depend on the DB activity (and you said yourself: you always get the same plan). But just because the plan is the same doesn't mean the execution time will be the same. Think about it: how can the a statement have the same runtime when the system is idle and when the CPU and the harddisk are fully utilized - it's simply not possible. – a_horse_with_no_name Feb 24 '14 at 21:42
  • 1
    @Alexandros: Run statements you want to compare under the same circumstances, multiple times, alternating. Compare the faster runs. This is still not 100 % reliable, but it's mostly good enough. – Erwin Brandstetter Feb 24 '14 at 23:47
  • 1
    @Alexandros I tend to use a dev system that's mostly idle for benchmarking, yes. As Erwin notes you can run it many times and average the results (even automating it with something like a custom pgbench script), but even then you'll have variation based on the server's average load at different times of day. – Craig Ringer Feb 25 '14 at 0:27
2
  1. No
  2. No
  3. Stop all other activity on the server, ensure that the cache is in a known state.

Elaboration:

  1. The client/interface does not change the SQL that is sent to the server and processed.
  2. In the scheme of things network latency is going to be under 1 sec, and probably a lot lot less, so not a significant factor.
  3. The best way to understand what is affecting how long a query takes is to understand what has changed from other times you have run the query. Is the data now cached and thus accessed much quicker, is the server waiting on a another query before it can start this query (i.e is the table locked), is there significant load on the server at this time etc. All of these factors and more need to be taken into account along with the execution analysis and plan analysis.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.