This repository was archived by the owner on Jan 28, 2021. It is now read-only.
This repository was archived by the owner on Jan 28, 2021. It is now read-only.
EXPLAIN only shows the first index #585
Closed
Description
I created 3 indexes:
CREATE INDEX a ON commit_files USING pilosa (commit_hash);
CREATE INDEX b ON commit_files USING pilosa (file_path);
CREATE INDEX c ON commit_files USING pilosa (blob_hash);
And then tried to show explain for query:
EXPLAIN FORMAT=TREE SELECT *
FROM commit_files
WHERE
commit_hash = 'd5b04a971262372a1531119d595cd4b04db2949a' AND
file_path = '.gitignore' AND
blob_hash = 'a7904a1efc6ef7d25b358c0f7ee33259fb4512e0';
EXPLAIN
shows that only index 'a'
will be used (but all 3 indexes should be used).
I suppose it's because of the function indexesIntersection
in assign_indexes.go
:
//...
for table, idx := range left {
if idx2, ok := right[table]; ok && canMergeIndexes(idx.lookup, idx2.lookup) {
idx.lookup = idx.lookup.(sql.SetOperations).Intersection(idx2.lookup)
idx.indexes = append(idx.indexes, idx2.indexes...)
}
//...
where we merge index b
into th a
and then index c
into intersection of a & b
. At the end we have one lookup a
merged with 2 operations (b, c). Moreover the name is still a
so explain shows only a
.
The same happens for other operations (not only for AND). We can change order of WHERE
conditions to see that always the first one is showed by explain