Skip to content
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
@kuba--

Description

@kuba--

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

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions