On Wed, Apr 17, 2019 at 11:43:13AM -0600, Keith Medcalf wrote:
>
> Your made up plans are intriguing. The plan you show for the latter query
> omit to join a and b. Are you just making things up?
The query plans were cut and pasted from the terminal. It's easy enough to
deduce where these plans came from: As someone else pointed out, the ",b" in
the second query shouldn't be there, so I removed it before generating the
query plans. That step of the query plan is irrelevant anyway. The point is
that in the subselect variant the query the planner chooses this
7 0 0 {USING ROWID SEARCH ON TABLE b FOR IN-OPERATOR}
which, given the conditions, is a far better choise than what the planner
chooses in the "join" variant:
8 0 0 {SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)}
It would be easy enough again for the planner to deduce this, but as Ryan
Smith described, may not be worth doing in the general case. I don't know.
I'm just reporting in from the field.
--
Poor Yorick
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
