Explain Plan
Explain Plan can be used to obtain a description of the strategy or plan that XAP uses to implement a specific SQLQuery
.
Explain Plan reports on the way in which the query uses indices and how many entries were scanned.
For detailed information how to assign index consult indexing
Index Information
- Indices that the space considered using and the selected index at each stage of the query.
- Each cluster node may produce a different result.
- Information breakdown by Pojo type.
Scanning Information
- Number of entries the space scanned in order to find the matching entries and how many entries were matched.
- Each cluster node may produce a different result.
- Information breakdown by Pojo type.
Index Types
The index type is determined by the SpaceIndexType enumeration. The index types are:
NONE - No indexing is used.
BASIC - Basic index is used - this speeds up equality matches (equal to/not equal to).
EXTENDED - Extended index - this speeds up comparison matches (bigger than/less than).
Usage
The execution plan is initiated by calling the withExplainPlan()
method on the SQLQuery
. After the query is executed the information is obtained with the query.getExplainPlan()
method.
SQLQuery<Person> query = new SQLQuery<Person>(Person.class, "age > 20")
.withExplainPlan();
gigaSpace.readMultiple(query);
ExplainPlan explainPlan = query.getExplainPlan();
Example
We use a Person
class with several properties annotated with @SpaceIndex
indicating that we want to use indexes when queries are performed.
import java.util.UUID;
import com.gigaspaces.annotation.pojo.SpaceClass;
import com.gigaspaces.annotation.pojo.SpaceId;
import com.gigaspaces.annotation.pojo.SpaceIndex;
@SpaceClass
public class Person {
private UUID id;
private String country;
private String gender;
private Long age;
@SpaceId
public UUID getId() {
return id;
}
@SpaceIndex
public String getCountry() {
return country;
}
@SpaceIndex
public String getGender() {
return gender;
}
@SpaceIndex
public Long getAge() {
return age;
}
.........
}
When different SQLQuery
are executed against the space, we can display for each of them the plan that XAP chooses to execute the query.
Query
SQLQuery<Person> query = new SQLQuery<Person>(Person.class, "age > ? ")
.setParameter(1, 30L)
.withExplainPlan();
gigaSpace.readMultiple(query);
System.out.println(query.getExplainPlan());
Output
******************** Explain plan report ********************
Query: SELECT * FROM sandbox.explainplan.Person WHERE age > ?
Execution Information Summary:
Total scanned partitions: 1
Total scanned entries: 1000
Total matched entries: 1000
Detailed Execution Information:
Query Tree:
AND
GT(age, 30)
Partition Id: 0
Index Information: NO INDEX USED
sandbox.explainplan.Person:
Scanned entries: 1000
Matched entries: 1000
*************************************************************
Query
query = new SQLQuery<Person>(Person.class, "age > ? and gender = ? and country = ?")
.setParameter(1, 30L)
.setParameter(2, "M")
.setParameter(3, "USA")
.withExplainPlan();
gigaSpace.readMultiple(query);
System.out.println(query.getExplainPlan());
Output
******************** Explain plan report ********************
Query: SELECT * FROM sandbox.explainplan.Person WHERE age > ? and gender = ? and country = ?
Execution Information Summary:
Total scanned partitions: 1
Total scanned entries: 1000
Total matched entries: 1000
Detailed Execution Information:
Query Tree:
AND
GT(age, 30)
EQ(country, USA)
EQ(gender, M)
Partition Id: 0
Scanned entries: 1000
Matched entries: 1000
Index scan report:
MATCH
Inspected:
[@1] EQ(country, USA), size=1000, type=BASIC
[@2] EQ(gender, M), size=1000, type=BASIC
Selected: [@1] EQ(country, USA), size=1000, type=BASIC
*************************************************************
Query
query = new SQLQuery<Person>(Person.class, "age > ? and gender = ? or country = ? ")
.setParameter(1, 30L)
.setParameter(2, "M")
.setParameter(3, "USA")
.withExplainPlan();
gigaSpace.readMultiple(query);
System.out.println(query.getExplainPlan());
Output
******************** Explain plan report ********************
Query: SELECT * FROM sandbox.explainplan.Person WHERE age > ? and gender = ? or country = ?
Execution Information Summary:
Total scanned partitions: 1
Total scanned entries: 1000
Total matched entries: 1000
Detailed Execution Information:
Query Tree:
OR
EQ(country, USA)
AND
EQ(gender, M)
GT(age, 30)
Partition Id: 0
Scanned entries: 1000
Matched entries: 1000
Index scan report:
AND
Inspected:
[@1] EQ(gender, M), size=1000, type=BASIC
[@2] GT(age, 30), size=unknown, type=BASIC, UNUSABLE
Selected: [@1] EQ(gender, M), size=1000, type=BASIC
AND
Inspected:
[@3] EQ(country, USA), size=1000, type=BASIC
Selected: [@3] EQ(country, USA), size=1000, type=BASIC
OR
Inspected:
[@3] EQ(country, USA), size=1000, type=BASIC
[@1] EQ(gender, M), size=1000, type=BASIC
Selected: [@4] Union [@3, @1]
*************************************************************
Limitations
- Supported via the Java API. Not supported via the .NET API.
- Supported operations: Read, ReadMultiple, TakeMultiple , count
- Supported index types: Basic and Extended. Compund , Unique , Collection , Geospatial index are not supported.
- Supported query type : Basic.
- Not Supported:
- Off-Heap
- FIFO grouping
- Operations: Take/Clear and variations, blocking operations, space iterator, aggregate , change, notifications
- JDBC API (hence also Web-UI/GS-UI)
- Regex, Is null, Sql function
- Not thread Safe