Skip to content
This repository has been archived by the owner on Jan 28, 2021. It is now read-only.

EXPLAIN only shows the first index #585

Closed
kuba-- opened this issue Jan 14, 2019 · 1 comment
Closed

EXPLAIN only shows the first index #585

kuba-- opened this issue Jan 14, 2019 · 1 comment
Assignees
Labels
bug Something isn't working

Comments

@kuba--
Copy link
Contributor

kuba-- commented Jan 14, 2019

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

@kuba-- kuba-- added the bug Something isn't working label Jan 14, 2019
@kuba--
Copy link
Contributor Author

kuba-- commented Jan 14, 2019

Definitively the issue exists in version "srcd/gitbase:v0.18.0"

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants