Hello, I have noticed high latency when calling

WriteRelationships

. The

UPDATE

SQL statement is surprisingly slow, postgres is creating a suboptimal plan, even after analyzing the table

relation_tuple

. Example deleting 3 relations: (see attachment) Anyone else experiencing a similar problem? I'm able to improve performance by creating extended statistics on table

relation_tuple

, but should I? Are there any recommendations related to this? Postgres: 15.3 SpiceDB: 1.43.0 https://cdn.discordapp.com/attachments/844600078948630559/1374365407774900285/explain_analyze_update.txt?ex=682dc907&is=682c7787&hm=8a8be1dfc43e2436ab78ca3cabe3a0b5abb0face8270eade40546bc98b15832c&

Is this something that didn't happen to your deployment in 1.42.0? We introduced recent changes to how queries are crafted so it uses a hint system, but postgres requires of an extension to be able to respond to these hints (https://github.com/ossc-db/pg_hint_plan). In principle these changes should not have changed how queries are performed unless an issue already existed. @Joey worked on this and should be able to assist.

Experiencing same behaviour with version 1.33.0

Yeah then it's not related to the latest release. Does running the same thing via "DeleteRelationships" yield the same result?

Very similar result

2025-05-21 08:59:31.097 UTC [176] LOG:  duration: 2.703 ms  plan:
    Query Text: UPDATE relation_tuple SET deleted_xid = $1 WHERE deleted_xid = $2 AND namespace = $3 AND object_id = $4 AND relation = $5 AND userset_namespace = $6 AND userset_object_id = $7
    Update on relation_tuple  (cost=0.56..8.77 rows=0 width=0) (actual time=2.701..2.701 rows=0 loops=1)
      ->  Index Scan using ix_relation_tuple_by_subject on relation_tuple  (cost=0.56..8.77 rows=1 width=14) (actual time=0.040..2.530 rows=1 loops=1)
            Index Cond: (((userset_object_id)::text = 'a76020d1-80d6-4d21-97fa-960b43c0efd3'::text) AND ((userset_namespace)::text = 'namespace2'::text) AND ((namespace)::text = 'namespace1'::text) AND ((relation)::text = 'relation1'::text))
            Filter: ((deleted_xid = '9223372036854775807'::xid8) AND ((object_id)::text = '13b68f32-83c2-428a-b935-dc7e46d837db'::text))
            Rows Removed by Filter: 994

that does not seem like a very similar result to me? it ran in 2ms, and used an index scan. The previous query removed almost 100K rows, this only 1K

The first sample is write relationships with 3 relation updates (delete). The queries I did manually is simplified, should simulate what a write relationships with a single update is doing.

its hitting the wrong index

yeah, as Joey noticed, the options you have are: - create your own custom index, at your own risk - install pg_plan_hint extension to get the hinting system enabled

@Joakim Lodén mind issuing a PR to add the hint to this update call?

Sure, I will try to get it done within a few days