EXPLAIN QUERY PLAN on QueryInterfaceRequest

[sobri909]

Hi! Long time no see 😄

Years back I put together a basic explain() for Database (#363) and it was good enough for my needs at the time. But these days I'm using more of GRDB's query interface, so throwing in raw SQL is a barrier given I don't have as easy access to it.

So now I've thrown together a new little extension on QueryInterfaceRequest, and figured I'd share.

extension QueryInterfaceRequest {
    func explain() throws {
        try Database.pool.read { db in
            let preparedRequest = try self.makePreparedRequest(db)
            print("SQL: \(preparedRequest.statement.sql)")
            for explain in try Row.fetchAll(db, sql: "EXPLAIN QUERY PLAN " + preparedRequest.statement.sql) {
                print("EXPLAIN: \(explain)")
            }
        }
    }
}

Example usage

let sampleAlias = TableAlias()
let request = SampleTypeConfig
    .annotated(withRequired: SampleTypeConfig.daySamples.aliased(sampleAlias).select(max(Column("date")).forKey("newestSampleDate")))
    .annotated(withRequired: SampleTypeConfig.daySamples.aliased(sampleAlias).select(max(Column("lastUpdated")).forKey("lastUpdated")))
    .groupByPrimaryKey()

do {
    try request.explain()
} catch {
    logger.error(error, subsystem: .database)
}

And example output

SQL: SELECT "sampleTypeConfig".*, MAX("daySample"."date") AS "newestSampleDate", MAX("daySample"."lastUpdated") AS "lastUpdated" FROM "sampleTypeConfig" JOIN "daySample" ON "daySample"."sampleTypeConfigId" = "sampleTypeConfig"."id" GROUP BY "sampleTypeConfig"."rowid"
EXPLAIN: [id:7 parent:0 notused:0 detail:"SCAN sampleTypeConfig"]
EXPLAIN: [id:9 parent:0 notused:0 detail:"SEARCH daySample USING COVERING INDEX DaySample_on_sampleTypeConfigId_date_lastUpdated (sampleTypeConfigId=?)"]

I don't really have a question. Just figured it'd be rude not to share. That, and I bet I'm doing it a silly way and you can point out why 😉

For reference here's the old one I used to use on Database (and probably still will use, when raw SQL suits the needs):

extension Database {
    func explain(query: String, arguments: StatementArguments = StatementArguments()) throws {
        for explain in try Row.fetchAll(self, sql: "EXPLAIN QUERY PLAN " + query, arguments: arguments) {
            print("EXPLAIN: \(explain)")
        }
    }
}

Cheers! And happy new year 😄

[groue]

Thank you @sobri909 for sharing ❤️ Happy 2023 🙂