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

user_4012392 0
Viewed 594

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?

explain jooq postgresql java
3 Answers

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();
user_10451420
user_5217990

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|
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+------------------------+
user_18109620
Related