java - 我可以使用 JOOQ 对查询运行“解释分析”吗？

Question

我可以explain analyze在 JOOQ 中运行查询吗？喜欢：

explain analyse select some, columns from some_table

但是在 PostgreSQL 数据库上使用 JOOQ 吗？

我找到了一个org.jooq.Explain带有方法的接口DSLContext.explain​(Query query)- 但它似乎只是用于EXPLAIN查询：

@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.

是否有任何明智的方法可以EXPLAIN ANALYZE从代码端在数据库上运行？

标签： javapostgresqljooqexplain

Answer 1

是的，你可以运行解释。例子

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

System.out.println(explain);

输出如下所示（对于 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 ||
|------------------------------------------------------------------------------|
+------------------------------------------------------------------------------+

解释还包含行和成本

    /**
     * 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();