Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

By using EXPLAIN one can see the cost of running a query. The cost is in artificial units, and they are based on pre-defined parameters. 1 is one disk fetch and a CPU cycle is 0.01 units, I believe. My question is why PostgreSQL does not automatically run a test upon installation to determine the real numbers based on the hardware? Shouldn't this be an obvious quick win?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

It the costs were constant there might be a marginal gain. The units are necessarily artificial as the various resources are not interchangeable. Even on the same system the costs can vary at different times.

  • If the CPU becomes fully loaded, then CPU cycles become more expensive.
  • On a multi-core system CPU cost may be less than observed by testing one CPU.
  • If the disks become busy the cost of a disk access increases.
  • Systems may have different disks with different performance (read cost). Cost will vary depending on which disk or disk subsystem the data is on.
  • Different distribution of data across disks may vary the performance.
  • Different placements of indexes and data may vary the performance.
  • Placement of data across disks of different performance will result in disk I/O having multiple costs depending on which data is being accessed.
  • Different types of queries (batch vs OLTP) may have different appropriate cost distributions.

Once the database is created, it may be possible to determine better metrics from the database. Use of the updated metrics may change loads in ways than make them invalid. There are margins beyond which behavior becomes relatively un-predicable and likely to seriously degrade.

share|improve this answer

What you're referring to is known as System Statistics in Oracle; I don't think Postgres supports these (yet?).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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