JOOQ隐式转换bigint为numeric致PostgreSQL索引失效问题咨询

我编写了一段简单的JOOQ查询代码：

jooq.select(TABLE_NAME.fields())
                .from(TABLE_NAME)
                .where(TABLE_NAME.ID.in(ids))
                .fetchInto(tableDTO.class);

其中ids为List<BigInteger>类型。JOOQ生成的SQL语句如下：

select 
"schema"."table_name"."id",
"schema"."table_name"."entity_id", 
"schema"."table_name"."code", 
"schema"."table_name"."created_date", 
...
from "schema"."table_name" where "schema"."table_name"."id" in (3623)

对应的tableDTO定义：

@Data
public class tableDTO{
    private BigInteger id;
...
}

数据库表DDL：

create table table_name
(
    id bigint default nextval('schema.table_name_id_seq'::regclass) not null primary key,
...
)

同时配置了forcedType将该表的ID等字段映射为BigInteger：

<forcedType>
 <name>DECIMAL_INTEGER</name>
 <includeExpression>.*\.(TABLE_NAME)\.(ID|PARENT_ID|ENTITY_ID)</includeExpression>
</forcedType>

遇到的问题 #

执行该查询时，JOOQ会导致PostgreSQL隐式将bigint转换为numeric，使得数据库采用并行顺序扫描而非索引扫描，查询计划显示：

Fetched result: +----------------------------------------------------------------------------------+
|QUERY PLAN                                                                        |
+----------------------------------------------------------------------------------+
|Gather  (cost=1000.00..1561941.21 rows=65315 width=1896)                          |
|  Workers Planned: 2                                                              |
|  ->  Parallel Seq Scan on table_name (cost=0.00..1554409.71 rows=27215 width=1896)|
|        Filter: ((id)::numeric = '3623'::numeric)                                 |
+----------------------------------------------------------------------------------+

但在DataGrip中执行相同SQL语句时，PostgreSQL会使用索引扫描：

Index Scan using table_name_pkey on table_name (cost=0.43..2.65 rows=1 width=1896)
  Index Cond: (id = 3623)

我已对该表执行vacuum和analyze操作，表状态正常。现咨询：该隐式转换为何发生？是JOOQ还是数据库的问题？

这个隐式转换的根源在JOOQ的类型映射配置，和数据库无关，具体原因如下：

1. forcedType配置的类型不匹配
   你使用DECIMAL_INTEGER作为forcedType的名称，这个类型在JOOQ中会把数据库的bigint字段映射到Java的BigInteger，但对应的SQL绑定类型是NUMERIC。当传入BigInteger类型的参数时，JOOQ会以NUMERIC类型将参数发送给PostgreSQL，而数据库的id字段是bigint，PostgreSQL为了匹配两边类型，会把bigint类型的id转换为numeric进行比较——索引是基于bigint创建的，转换后的字段无法使用原索引，因此触发了全表扫描。

2. DataGrip无转换的原因
   在DataGrip中直接执行SQL时，输入的3623会被PostgreSQL自动识别为bigint类型（匹配字段类型），不需要类型转换，因此能正常使用索引扫描。

解决方案 #

修改JOOQ的forcedType配置，将DECIMAL_INTEGER替换为BIGINT，同时显式指定Java类型为BigInteger：

<forcedType>
  <name>BIGINT</name>
  <javaType>java.math.BigInteger</javaType>
  <includeExpression>.*\.(TABLE_NAME)\.(ID|PARENT_ID|ENTITY_ID)</includeExpression>
</forcedType>

这样配置后，JOOQ会将数据库的bigint字段映射到Java的BigInteger，同时在生成查询时，参数会以bigint类型发送给PostgreSQL，避免类型转换，让数据库可以正常使用索引扫描。

另外，也可以检查项目中是否存在其他类型映射冲突，确保JOOQ生成的SQL参数类型与数据库字段类型完全匹配。

内容的提问来源于stack exchange，提问作者True Seeker