I’m trying to debug a slow UPDATE statement that seems to put most of its time into the Plan phase of execution.
The statement tries to do a conditional update as follows:
UPDATE resources SET version_id = CASE WHEN (resources.json = $5) AND (resources.version_type = $6) THEN resources.version_id ELSE gen_random_uuid() END, operation = CASE WHEN $4 = _ THEN _ WHEN resources.operation = _ THEN _ WHEN (resources.json = $5) AND (resources.version_type = $6) THEN _ ELSE _ END, updated_at = CASE WHEN (resources.json = $5) AND (resources.version_type = $6) THEN resources.updated_at ELSE greatest(statement_timestamp(), resources.updated_at + _::INTERVAL) END, json = $5, version_type = $6 WHERE (((tenant = $1) AND (id = $2)) AND (resource_type = $3)) AND (version_id = $7) RETURNING operation, updated_at, version_id.
The timings CockroachDB reports are as follows:
Plan: 120.7ms avg latency (73ms std dev)
I can’t quite figure out why it would take so long to plan the query. Is it the many
greatest check for strict monotonicity, the gen_random_uuid() latency, or something else?