2

If I use postgres's EXPLAIN command, there's a top-level "cost". Assuming that the explain is accurate (ie despite the cost being in reality quite unreliable and/or inconsistent), what is the very approximate conversion from cost to minutes/seconds query duration (for a "large" cost)?

In my case, the query cost is 60 million

For context, my hardware is a regular laptop and the data is 12M rows joining to 250K rows on an indexed column, grouped on several columns to produce 1K rows of output.

This question is not about the query itself per se - there could be better ways to code the query. This question is also not about how inaccurate, unreliable or inconsistent the explain output is.

This question is about estimating the run time a query would take if it executed given its EXPLAIN cost and given that the EXPLAIN output is in fact an accurate analysis of the query.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Possible duplicate of [How reliable is the cost measurement in PostgreSQL Explain Plan?](http://stackoverflow.com/questions/21154780/how-reliable-is-the-cost-measurement-in-postgresql-explain-plan) – coreyward Jan 03 '17 at 22:10
  • 1
    There isn't a linear correlation between query cost estimate and execution time. [More info is here.](https://blog.pgaddict.com/posts/common-issues-with-planner-statistics) – coreyward Jan 03 '17 at 22:16
  • Doesn't have to be linear. Any kind of relationship is fine. – Bohemian Jan 03 '17 at 22:16
  • Not really a duplicate. The other Q asks how reliable. I'm asking for a currency exchange rate from cost to seconds (even if the cost is unreliable). – Bohemian Jan 03 '17 at 22:18
  • 3
    I would encourage you to skim the link I provided above; this question is akin to asking what the elapsed time to consume an amount of food is given its price. – coreyward Jan 03 '17 at 22:22
  • Also of interest: http://www.vldb.org/pvldb/vol9/p204-leis.pdf – coreyward Jan 03 '17 at 22:24
  • 1
    @coreyward, your food consumption time/cost example is the best analogy I've heard for this. – JNevill Jan 04 '17 at 13:42

0 Answers0