Hi List,
I was looking at the query plan of a rather simple query, but I don't
understand why sqlite would choose this query plan.
For the following example:
create table aaa(id INTEGER, name_id INTEGER, type CHAR);
create table bbb(name_id INTEGER, name CHAR);
create index ix_aaa ON aaa(id);
create index ix_bbb ON bbb(name_id);
.explain ON
explain query plan
SELECT aaa1.name_id,
bbb1.name
FROM aaa aaa1,
aaa aaa2 ON aaa1.id = aaa2.id,
bbb bbb1 ON bbb1.name_id = aaa1.name_id
WHERE aaa1.type = 'A'
AND aaa2.type <> 'A';
=========== output ==============
SELECT item[0] = {0:1}
item[1] = {2:1}
FROM {0,*} = aaa (AS aaa1)
{1,*} = aaa (AS aaa2)
{2,*} = bbb (AS bbb1)
WHERE AND(AND(AND(EQ({0:2},'A'),NE({1:2},'A')),EQ({0:0},{1:0})),EQ({2:0},{0:1}))
END
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE aaa AS aaa1 USING AUTOMATIC COVERING
INDEX (type=?)
0 1 1 SEARCH TABLE aaa AS aaa2 USING INDEX ix_aaa (id=?)
0 2 2 SEARCH TABLE bbb AS bbb1 USING INDEX ix_bbb
(name_id=?)
Sqlite decides to create an AUTOMATIC INDEX (time complexity O(n log n)) which
it then uses to iterate table aaa1. This index is not re-used for anything else
(it can't be re-used since 'type' is not used anywhere else) so only the
traversal of table aaa1 benefits from this index. However, I think, a full
table scan of aaa1 (time complexity O(n)) would always be faster, since for
creating the index it has to read that entire table anyway.
I was surprised that sqlite came up with the inferior query plan.
What makes sqlite think that creating + using an automatic index (for the outer
loop) makes the query faster the a full scan + filtering records? Is the
estimation of the costs for some action very bad for this query? Can I somehow
show the costs of a query plans (or of the rejected query plans)?
I'm using sqlite version 3.8.4.3
Note: After an "analyze aaa" (on a decently populated table) sqlite chooses the
full table scan instead of creating an automatic index (but our application
never uses 'analyze' to avoid that other (bad performing) query plans are used
during operation than during testing).
Note 2: Adding "NOT INDEXED" to aa1 gives the desired query plan, but of course
I prefer that sqlite choses the right query plan.
Regards,
Rob Golsteijn
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
