Search code examples
postgresqlkotlinjooq

Jooq casts my UUID even though its already of type uuid, why?

CREATE TABLE IF NOT EXISTS events
(
      id                    UUID NOT NULL,
      type_id       UUID,
      occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,
      PRIMARY KEY (id)
);

jood code:

val queryDsl = dslContext.select(EventFields).from(EVENTS)
                .where(
                    (EVENTS.TYPE_ID.isNull)
                        .or(EVENTS.TYPE_ID.eq(query.typeId.id))
                )

model id code:

import java.util.UUID

data class TypeId(val id: UUID) {
    constructor (id: String) : this(UUID.fromString(id))

    override fun toString(): String = id.toString()
}

Query:

where ("public"."events"."type_id" = cast('d0c6a2a7-2032-4dd7-b2b1-77a554262af7' as uuid))

Why cast?

I tried to get rid of this casting as it affects my index performance

My index: CREATE INDEX company_employee_occurred_at_idx ON balance_state_event (id, type_id, occurred_at DESC);

In my explain query plain one of the line is: Filter: ((type_id IS NULL) OR (type_id = '390caffd-9c3b-4ea4-b5e2-6ae342268141'::uuid)), why is there an explicit filter if the index is there

Solution

  • Why cast?

    There has been a relatively recent fix in jOOQ where all UUID bind values are now cast to their type explicitly for SQLDialect.POSTGRES:

    The fix has been applied to versions:

    • 3.20.0
    • 3.19.6
    • 3.18.13
    • 3.17.22

    It is necessary in a lot of edge cases and not-so-edge cases. jOOQ doesn't hard-code all of these cases to make a more specific decision based on the context, it just casts all UUID typed bind values, just like it auto-casts all of these typed bind values:

    • Enum typed
    • Interval typed
    • JSON typed
    • XML typed
    • Spatial typed

    And possibly more, in the future.

    This answers the question you asked (the one from the title).

    I tried to get rid of this casting as it affects my index performance

    I don't think this cast affects your performance. The bind value is a constant, and casting it doesn't change anything. Without the cast, there would still be an implicit cast as types get promoted.

    However, your OR predicate is a more likely problem:

    ((type_id IS NULL) OR (type_id = '390caffd-9c3b-4ea4-b5e2-6ae342268141'::uuid))

    Do check why in your case, no BitmapOr operation appears in your execution plan. It is hard to say from the little information you provided. In a more minimal example, this works just fine:

    create table t (i uuid);
create index i on t (i);
insert into t select uuid_generate_v4() from generate_series(1, 1000000) as t (i);

explain
select *
from t
where i is null or i = cast('390caffd-9c3b-4ea4-b5e2-6ae342268141' as uuid)

    Producing:

    |QUERY PLAN                                                                         |
|-----------------------------------------------------------------------------------|
|Bitmap Heap Scan on t  (cost=8.87..12.88 rows=1 width=16)                          |
|  Recheck Cond: ((i IS NULL) OR (i = '390caffd-9c3b-4ea4-b5e2-6ae342268141'::uuid))|
|  ->  BitmapOr  (cost=8.87..8.87 rows=1 width=0)                                   |
|        ->  Bitmap Index Scan on i  (cost=0.00..4.43 rows=1 width=0)               |
|              Index Cond: (i IS NULL)                                              |
|        ->  Bitmap Index Scan on i  (cost=0.00..4.43 rows=1 width=0)               |
|              Index Cond: (i = '390caffd-9c3b-4ea4-b5e2-6ae342268141'::uuid)       |

    This shows that you probably have another question to ask, best in a new question, as this one is about jOOQ and casting, not about your actual performance issue.