2

For SQLite, given a SQL query Q, I am trying to figure out how to get the estimated query execution cost for Q out of the SQLite's query optimizer using C++ API.

I've searched for this problem, found lots of discussions about this cost on SQLite's website and how it is used internally by the query optimizer. But I cannot locate any C++ API call for getting this cost.

Hence, I'd guess maybe such a call is not implemented, but perhaps someone might know a way of getting this cost out of SQLite?

Adam Lee
  • 21
  • 2

1 Answers1

0

The documentation says:

The data returned by the EXPLAIN QUERY PLAN command is intended for interactive debugging only. The output format may change between SQLite releases. Applications should not depend on the output format of the EXPLAIN QUERY PLAN command.

Anything that is part of the API would need to be supported forever, so the details of the query optimizer cannot be made part of the API.

If you really want those values and know what you're doing, you could modify the SQLite source code to return them.

The sqlite3_stmt_status() function allows you to retrieve some performance-related metrics of a statement, but these are not estimates but the actual cost after execution.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks! I was hoping someone has already looked at the source and knows how to do that :) I've – Adam Lee Jul 25 '16 at 15:39
  • Thanks! I was hoping someone has already looked at the source code and knows how to do that :) I've looked at the SQLite’s source code myself searching for "cost", but could not figure out quickly how to get such an estimate. To be precise, given two different SQL queries I need to be able to tell which one is more expensive according to the SQLite's optimizer, before these queries are executed. I don’t need an estimation of the actual running time per se. – Adam Lee Jul 25 '16 at 15:45
  • See `nQueryLoop` in the `Parse` structure. `sqlite3WhereEnd()` recursively restores the old value, so you should save it to another field in `Parse`, and copy it to the statement in `sqlite3Prepare()`. – CL. Jul 25 '16 at 16:15