Query Cost

Query Optimisation

Query optimisation involves choosing the evaluation plan with the smallest query cost of all possible equivalent options.

Query Cost

Factors that come into calculating time cost of query are * number of disk accesses * resource availability (CPU, memory) * network communication speed (for distributed or server-client databases)

Estimates

• number of seeks * average seek cost
• number of blocks read * average block read cost
• number of blocks written * average block write cost

In our case, we will use number of block transfers and number of seeks as the cost measures. The cost is the sum of total time spent on block transfer and seeking data. \(\(\omega = b * t_T + S * t_S\)\)

Reasons for Inaccuracy

• Does not account for data stored in cache / buffer, which has faster IO. This is incredibly tough to estimate.
• Amount of actual memory available depends on other processes executing in the system, which cannot be consistently known or reliably estimated.
• We use (optimistic) estimates of data stored in buffer and memory needed for operations, but those are just estimates.