Is there a good "rule of thumb" for translating EXPLAIN cost to (wall-clock) runtime?

From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have trouble trying to translate this to "ask me again in 15 minutes". Does anyone know a good "rule of thumb" to use for any particular database?

I realize this isn't going to be a hard and fast rule, but even being able to give a ballpark figure could be useful in some instances.

I suspect that this is not possible the way you imagine it. One important reason is that the actual run time is very hardware dependent, and many of the optimization decisions that the database engine does are effectively about balancing the use of the different hardware components (e.g., disk, memory, CPU).

I suggest that you run a bunch of queries relevant to your application, record the cost estimations and the run times, and try to make sense of that data. You might get a nice linear relationship, or you might learn that the cost numbers are worthless for this purpose.

StackExchange Database Administrators Q#262, answer score: 6