¿Puedo ejecutar el explain analyze en una consulta en JOOQ? me gusta:
explain analyse select some, columns from some_table¿pero hacerlo usando JOOQ en la base de datos PostgreSQL?
Encontré una interfaz org.jooq.Explain , con un método DSLContext.explain(Query query) - pero parece solo usar EXPLAIN en una consulta:
@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. ¿Hay alguna forma sensata de ejecutar un EXPLAIN ANALYZE en la base de datos desde el lado del código?
Sí, puedes ejecutar explicar. Ejemplo
SelectWhereStep<ModuldefRecord> where = dsl.selectFrom(MODULDEF); Explain explain = dsl().explain(where); System.out.println(explain);La salida se ve así (para 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 || |------------------------------------------------------------------------------| +------------------------------------------------------------------------------+Explicar también contiene filas y costos.
/** * 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();Todavía no es compatible: https://github.com/jOOQ/jOOQ/issues/10424 . En su lugar, use plantillas SQL simples :
ctx.fetch("explain analyze {0}", select);Para mariadb necesitaba hacer:
SelectConditionStep<TableNameRecord> select = context.selectFrom(Tables.TABLE_NAME) .where(filter); System.out.println(context.fetch("analyze " + select.getSQL(ParamType.INLINED)));que produjo la salida:
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+-------+--------+----------+------------------------+ | 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| +----+-----------+----------+-----+-----------------+-----------------+-------+------+----+-------+--------+----------+------------------------+ Si usa context.explain(select) como lo propone otra respuesta, perderá algunas columnas:
+----+-----------+----------+-----+-----------------+-----------------+-------+------+----+------------------------+ | 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| +----+-----------+----------+-----+-----------------+-----------------+-------+------+----+------------------------+