Can I run an "explain analyze" on a query using JOOQ?

Question

Can I run explain analyze on a query in JOOQ? like:

explain analyse select some, columns from some_table

but do it using JOOQ on PostgreSQL database?

I have found an interface org.jooq.Explain, with a method DSLContext.explain​(Query query) - but it seems just to use EXPLAIN on a query:

@Support({AURORA_MYSQL,AURORA_POSTGRES,H2,HSQLDB,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,SQLITE}) 
Explain explain​(Query query)
Run an EXPLAIN statement in the database to estimate the cardinality of the query.

Is there any sensible way to run an EXPLAIN ANALYZE on the database from the code side?

Answer 1

Yes you can run explain. Example

SelectWhereStep<ModuldefRecord> where = dsl.selectFrom(MODULDEF);
Explain explain = dsl().explain(where);

System.out.println(explain);

The output look like this (for Oracle)

+------------------------------------------------------------------------------+
|PLAN_TABLE_OUTPUT                                                             |
+------------------------------------------------------------------------------+
|Plan hash value: 3871168833                                                   |
|                                                                              |
|------------------------------------------------------------------------------|
|| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     ||
|------------------------------------------------------------------------------|
||   0 | SELECT STATEMENT  |          | 61303 |    30M|  1305   (1)| 00:00:01 ||
||   1 |  TABLE ACCESS FULL| MODULDEF | 61303 |    30M|  1305   (1)| 00:00:01 ||
|------------------------------------------------------------------------------|
+------------------------------------------------------------------------------+

Explain also contains rows and cost

    /**
     * The number of rows (cardinality) that is estimated to be returned by the query.
     * <p>
     * This returns {@link Double#NaN} if rows could not be estimated.
     */
    double rows();

    /**
     * The cost the database associated with the execution of the query.
     * <p>
     * This returns {@link Double#NaN} if cost could not be retrieved.
     */
    double cost();

Answer 2

It's not supported yet: https://github.com/jOOQ/jOOQ/issues/10424. Use plain SQL templating, instead:

ctx.fetch("explain analyze {0}", select);

Answer 3

For mariadb I needed to do:

SelectConditionStep<TableNameRecord> select =
    context.selectFrom(Tables.TABLE_NAME)
    .where(filter);
System.out.println(context.fetch("analyze " + select.getSQL(ParamType.INLINED)));

which produced the output:

+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+-------+--------+----------+------------------------+
|  id|select_type|table     |type |possible_keys    |key              |key_len|ref   |rows|r_rows |filtered|r_filtered|Extra                   |
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+-------+--------+----------+------------------------+
|   1|SIMPLE     |table_name|range|table_column_name|table_column_name|20     |{null}|1000|1000.00|   100.0|     100.0|Using where; Using index|
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+-------+--------+----------+------------------------+

If you use context.explain(select) as proposed by another answer you lose a few columns:

+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+------------------------+
|  id|select_type|table     |type |possible_keys    |key              |key_len|ref   |rows|Extra                   |
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+------------------------+
|   1|SIMPLE     |table_name|range|table_column_name|table_column_name|20     |{null}|1000|Using where; Using index|
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+------------------------+