Jooq implicitly casts bigint to numeric and index doesn't work

Question

I have very simple JOOQ query:

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

where ids is List<BigInteger>

Query that JOOQ generates:

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

table DDL:

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

Also, there is a forcedType for this table to cast field ID to BigInteger:

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

Issue

When I run this query via JOOQ, somehow it casts list of bigint to numeric and postgres uses parallel seq scan instead of index scan.

I can see that by printing jooq query plan:

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)                                 |
+----------------------------------------------------------------------------------+

But when I run the exact same query via DataGrip, Postgres is using index scan:

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

I already tried to vacuum, analyze this table. It seems that my table is perfectly fine and somehow jooq generates those faulty casts

Question

Why does this implicit cast happen? Is this a JOOQ's fault or DB's?

Answer 1

Why does this happen

You applied a data type rewrite, which means that jOOQ's code generation erases all knowledge of the original data type (BIGINT) and pretends that the data type reported by the PostgreSQL INFORMATION_SCHEMA.COLUMNS view is indeed NUMERIC.

Now, for numerous reasons, jOOQ tends to cast bind values in PostgreSQL as e.g. CAST(? AS NUMERIC) as PostgreSQL tends to be unable to infer the type, so when PostgreSQL sees a comparison like:

BIGINT_COLUMN = CAST(? AS NUMERIC)

Then there are 4 options of how to process this in PostgreSQL:

• Reject the "incompatible" comparison (undesirable)
• Override the = operator for all possible pairs of types (impractical)
• Promote the less precise type (BIGINT to NUMERIC). This is the preferred solution, because it's not lossy
• "Demote" the more precise type (NUMERIC to BIGINT) (undesirable, because lossy)

But when BIGINT_COLUMN is promoted to NUMERIC, the optimiser seems to be unable to use an index, unless the index is a function based index on CAST(BIGINT_COLUMN AS NUMERIC). The jOOQ blog documents a similar problem of undesired Oracle DATE -> TIMESTAMP promotions.

How to fix it

I don't know why you need a BigInteger type in your client code, when the database can store only Long values. The simplest fix is to simply not do that and work with Long instead. If you prefer BigInteger nonetheless, you can either:

• Change the schema to store NUMERIC values as well (that will prevent overflows when storing too large IDs)
• Attach a Converter<Long, BigInteger> to the column instead of applying data type rewriting. That way, jOOQ will produce BigInteger values in client code without losing information about the underlying column type.