After reading this article https://www.sqlite.org/rtree.html about the R*Tree in SQLite, i am currently experimenting with a 2-Dim R-Tree in a Core Data model. Particularly i expected (maybe somewhat naively) some kind of select statement on the index table but i did not see any in the SQLite debug trace when executing a fetch statement on the Region entity with indexed attributes (see predicateBoundaryIdx in the code below).
My questions are: how must a Core Data model (entities, attributes) and the NSPredicate look like in order to benefit from the R-Tree index?
[XCode v11.4, iOS v13.1, Swift. Switched on com.apple.CoreData.SQLDebug 4]
Model
Index
Corresponding database scheme
CREATE TABLE ZPERSON ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZLOCATION INTEGER, Z1CONTACTS INTEGER, ZNAME VARCHAR );
CREATE TABLE ZREGION ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZMAXLATITUDE FLOAT, ZMAXLATITUDEIDX FLOAT, ZMAXLONGITUDE FLOAT, ZMAXLONGITUDEIDX FLOAT, ZMINLATITUDE FLOAT, ZMINLATITUDEIDX FLOAT, ZMINLONGITUDE FLOAT, ZMINLONGITUDEIDX FLOAT, ZNAME VARCHAR );
CREATE INDEX ZPERSON_ZLOCATION_INDEX ON ZPERSON (ZLOCATION);
CREATE INDEX ZPERSON_Z1CONTACTS_INDEX ON ZPERSON (Z1CONTACTS);
CREATE VIRTUAL TABLE Z_Region_RegionIndex USING RTREE (Z_PK INTEGER PRIMARY KEY, ZMINLATITUDEIDX_MIN, ZMINLATITUDEIDX_MAX, ZMAXLATITUDEIDX_MIN, ZMAXLATITUDEIDX_MAX, ZMINLONGITUDEIDX_MIN, ZMINLONGITUDEIDX_MAX, ZMAXLONGITUDEIDX_MIN, ZMAXLONGITUDEIDX_MAX)
/* Z_Region_RegionIndex(Z_PK,ZMINLATITUDEIDX_MIN,ZMINLATITUDEIDX_MAX,ZMAXLATITUDEIDX_MIN,ZMAXLATITUDEIDX_MAX,ZMINLONGITUDEIDX_MIN,ZMINLONGITUDEIDX_MAX,ZMAXLONGITUDEIDX_MIN,ZMAXLONGITUDEIDX_MAX) */;
CREATE TABLE IF NOT EXISTS "Z_Region_RegionIndex_rowid"(rowid INTEGER PRIMARY KEY,nodeno);
CREATE TABLE IF NOT EXISTS "Z_Region_RegionIndex_node"(nodeno INTEGER PRIMARY KEY,data);
CREATE TABLE IF NOT EXISTS "Z_Region_RegionIndex_parent"(nodeno INTEGER PRIMARY KEY,parentnode);
Code for testing
func application(_ application: UIApplication, didFinishLaunchingWithOptions launchOptions: [UIApplication.LaunchOptionsKey: Any]?) -> Bool {
let mainContext: NSManagedObjectContext
mainContext = persistentContainer.viewContext
mainContext.mergePolicy = NSMergeByPropertyObjectTrumpMergePolicy
mainContext.undoManager = nil
mainContext.shouldDeleteInaccessibleFaults = true
mainContext.automaticallyMergesChangesFromParent = true
var personObj: Person
var locationObj: Region
let n = 1000000
let personNr = stride(from: 1, through: n+1, by: 1).map(String.init).shuffled()
for i in 1...n
{
personObj = Person(context: mainContext)
locationObj = Region(context: mainContext)
locationObj.name = "Region \(i)"
locationObj.minlatitude = 40.000000 - Float.random(in: 0 ..< 5)
locationObj.minlongitude = 9.000000 - Float.random(in: 0 ..< 5)
locationObj.maxlatitude = 40.000000 + Float.random(in: 0 ..< 5)
locationObj.maxlongitude = 9.000000 + Float.random(in: 0 ..< 5)
locationObj.minlatitudeidx = locationObj.minlatitude
locationObj.minlongitudeidx = locationObj.minlongitude
locationObj.maxlatitudeidx = locationObj.maxlatitude
locationObj.maxlongitudeidx = locationObj.maxlongitude
personObj.name = "Person \(personNr[i])"
personObj.location = locationObj
if i % 1000 == 0 {
saveContext()
}
}
saveContext()
let request: NSFetchRequest<Region> = Region.fetchRequest()
let requestIdx: NSFetchRequest<Region> = Region.fetchRequest()
let eps : Float = 1.0
let predicateBoundaryIdx = NSPredicate(format: "(minlatitudeidx >= %lf and maxlatitudeidx =< %lf) and (minlongitudeidx >= %lf and maxlongitudeidx =< %lf)",40.000000-eps,40.000000+eps,9.000000-eps,9.000000+eps)
let predicateBoundary = NSPredicate(format: "(minlatitude >= %lf and maxlatitude =< %lf) and (minlongitude >= %lf and maxlongitude =< %lf)",40.000000-eps,40.000000+eps,9.000000-eps,9.000000+eps)
requestIdx.predicate = predicateBoundaryIdx;
request.predicate = predicateBoundary;
print("fetch index:")
do {
let result = try mainContext.count(for:requestIdx)
print("Count = \(result)")
} catch {
print("Error: \(error)")
}
print("fetch no index:")
do {
let result = try mainContext.count(for:request)
print("Count = \(result)")
} catch {
print("Error: \(error)")
}
for store in (persistentContainer.persistentStoreCoordinator.persistentStores) {
os_log("Store URL: %@", log: Debug.coredata_log, type: .info, store.url?.absoluteString ?? "No Store")
}
return true
}
Core Data SQL Trace
CoreData: sql: SELECT COUNT( DISTINCT t0.Z_PK) FROM ZREGION t0 WHERE ( t0.ZMINLATITUDEIDX >= ? AND t0.ZMAXLATITUDEIDX <= ? AND t0.ZMINLONGITUDEIDX >= ? AND t0.ZMAXLONGITUDEIDX <= ?)
