Details
-
Improvement
-
Resolution: Fixed
-
Major
-
4.6.0
-
None
Description
Request
When a query has several possible indexes to choose from, I'd like it to use the indexes that better match the query's WHERE clauses.
Example
Query
SELECT u.id, u.`key`, u.first_name, u.last_name, u.dob, u.address.country, c.name, u.email_opt_in, u.email, u.groups
|
FROM default AS u JOIN default AS c ON KEYS "Country::" || u.address.country
|
WHERE u.state = "active" AND IFMISSING(u.sync_to_salesforce, true) = true AND meta(u).id LIKE "User::%"
|
AND (u.email_opt_in = True)
|
UNION
|
SELECT u.id, u.`key`, u.first_name, u.last_name, u.dob, u.address.country, c.name, u.email_opt_in, u.email, u.groups
|
FROM default AS u JOIN default AS c ON KEYS "Country::" || u.address.country
|
WHERE u.state = "active" AND IFMISSING(u.sync_to_salesforce, true) = true AND meta(u).id LIKE "User::%"
|
AND (any g in u.groups satisfies g="Group::tcgo::normal" end);
|
When I have just these to indexes, the query uses them
create index i2 ON `default`
|
(state, ifmissing(sync_to_salesforce,true),meta().id,id, email_opt_in,groups,`key`, first_name, last_name, dob, address.country, name, email, groups )
|
WHERE state = 'active' and (ifmissing(sync_to_salesforce, true) = true) and meta().id like "User::%"
|
AND email_opt_in = True
|
|
|
create index i3 ON `default`
|
(state, ifmissing(sync_to_salesforce,true),meta().id,id, email_opt_in,groups,`key`, first_name, last_name, dob, address.country, name, email, groups )
|
WHERE state = 'active' and (ifmissing(sync_to_salesforce, true) = true) and meta().id like "User::%"
|
AND (ANY g IN groups satisfies g="Group::tcgo::normal" END)
|
However, if I create a broader index in addition to the ones from above, the query switches to using this broader index instead
create index i1 ON `default`(state,ifmissing(sync_to_salesforce,true),meta().id,id,`key`,first_name,last_name,dob,address.country,name,email_opt_in,email,groups)
|
WHERE state = 'active' and (ifmissing(sync_to_salesforce, true) = true) and meta().id like "User::%"
|
Note that a simplified version of this query works as expected, i.e. it uses better-matching indexes instead of a broader on.
For example, the following query uses i2 and i3 instead of i1
create index i1 on t(a) where state = 'CA';
|
create index i2 on t(a) where state = 'CA' and region = 'North';
|
create index i3 on t(a) where state = 'CA' and zip = '90210';
|
|
|
SELECT a FROM t WHERE state = 'CA' and region = 'North' and a is not missing
|
UNION
|
SELECT a FROM t WHERE state = 'CA' and zip = '90210' and a is not missing;
|