On 8/11/17, Wout Mertens <wout.mert...@gmail.com> wrote:
> Aha ok, great!
> Now, forgive me, but there is still a difference in the byte code, and I'm
> having a hard time decyphering it. The difference from the non-distinct and
> the distinct is:
> * The table t1 is opened
Yes, there is a little work done to open a cursor on the main table,
but that cursor is never used.
The reason that the cursor is opened is because at the top of the loop
where the cursor needs to be opened, the query planner still does not
realize that it does not actually need the cursor.
> * There is an extra Seek in the scanning loop, it looks like it moves the
> read pointer of table t1 to the indexed rowid. There are no reads
> occurring, but won't the seek cause I/O?
That seek is deferred. In fact, we changed the name of the opcode to
DeferredSeek in 3.20.0 to avoid confusion. The actual work of seeking
on the main table cursor is deferred until there is a read request on
that cursor. And no read request every occurs, and so the seek never
D. Richard Hipp
sqlite-users mailing list