Support `.eqp` like in sqlite3

[asm0dey]

There is an awesome .eqp on command in the default sqlite3 shell. It works like this:

sqlite> .eqp on
sqlite> select distinct book.id, (select coalesce(json_group_array(json_array(v0, v1, v2, v3, v4, v5, v6, v7)), json_array()) from (select b.id as v0, b.path as v1, b.name as v2, b.date as v3, b.added as v4, b.sequence as v5, b.sequence_number as v6, b.lang as v7 from book as b where b.id = book.id) as t) as book, (select coalesce(json_group_array(json_array(v0, v1, v2, v3, v4, v5, v6)), json_array()) from (select distinct author.id as v0, author.fb2id as v1, author.first_name as v2, author.middle_name as v3, author.last_name as v4, author.nickname as v5, author.added as v6 from author join book_author on book_author.author_id = author.id where book_author.book_id = book.id) as t) as authors, (select coalesce(json_group_array(json_array(v0)), json_array()) from (select distinct genre.name as v0 from genre join book_genre on book_genre.genre_id = genre.id where book_genre.book_id = book.id) as t) as genres from book join book_author on book_author.book_id = book.id where (book.sequence = 'Звёздные войны' and book_author.author_id = 45826) order by book.sequence_number asc nulls last, book.name
   ...> ;
QUERY PLAN
|--SEARCH book_author USING INDEX book_author_author_id (author_id=?)
|--SEARCH book USING INTEGER PRIMARY KEY (rowid=?)
|--CORRELATED SCALAR SUBQUERY 2
|  `--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
|--CORRELATED SCALAR SUBQUERY 4
|  |--CO-ROUTINE t
|  |  |--SEARCH book_author USING COVERING INDEX sqlite_autoindex_book_author_1 (book_id=?)
|  |  |--SEARCH author USING INTEGER PRIMARY KEY (rowid=?)
|  |  `--USE TEMP B-TREE FOR DISTINCT
|  `--SCAN t
|--CORRELATED SCALAR SUBQUERY 6
|  |--CO-ROUTINE t
|  |  |--SEARCH book_genre USING COVERING INDEX sqlite_autoindex_book_genre_1 (book_id=?)
|  |  |--SEARCH genre USING INTEGER PRIMARY KEY (rowid=?)
|  |  `--USE TEMP B-TREE FOR DISTINCT
|  `--SCAN t
|--USE TEMP B-TREE FOR DISTINCT
`--USE TEMP B-TREE FOR ORDER BY
122718|[[122718,"/media/sda3/Books/native/439383.fb2","Заря джедаев: В пустоту","","2022-06-19 23:10:14.992363471","Звёздные войны",1,"ru"]]|[[45826,null,"Тим",null,"Леббон",null,"2022-06-19 20:51:32.016737954"]]|[["sf_space"]]
264789|[[264789,"/media/sda3/Books/native/577219.fb2","Заря джедаев: В бесконечность","","2022-06-20 09:48:50.7587699","Звёздные войны",null,"ru"]]|[[45826,null,"Тим",null,"Леббон",null,"2022-06-19 20:51:32.016737954"]]|[["sf_space"]]
173283|[[173283,"/media/sda3/Books/native/439488.fb2","Заря джедаев: В пустоту","","2022-06-20 03:09:41.412632853","Звёздные войны",null,"ru"]]|[[45826,null,"Тим",null,"Леббон",null,"2022-06-19 20:51:32.016737954"]]|[["sf_space"]]

As you can see it outputs the execution plan tree on each request. Would be nice to support it in litecli too

[oz123]

This is not so easy since eqp, since it's a special command. One will have to create some "storage" for eqp varaible and probably insert EXPLAIN QUERY PLAN before the query. Once can't just do:

cursor.execute(".eqp on")

The underlying C / Python binding don't allow it.

[asm0dey]

Do I understand correctly that SQLite doesn't expose these query plans over API and they are somehow hardcoded into the CLI?

[oz123]

I haven't looked into the SQLite code itself. However it's not exposed in the python driver, not even with apsew.