Sleipnir
03/16/2022, 1:34 PMJoey
03/16/2022, 1:37 PMJoey
03/16/2022, 1:38 PMSleipnir
03/16/2022, 3:30 PMdefinition object {
permission view
permission view_if_granted
permission grant_view
}
has_permission = checkPermission("object:1", "view", "user:2")
if not has_permission then
has_permission = checkPermission("object:1", "view_if_granted", "user:2")
and checkPermission("object:1", "grant_view", "application:3")
end
Sleipnir
03/16/2022, 3:42 PMwilliamdclt
03/17/2022, 11:27 AMSELECT MIN(id), MAX(id) FROM relation_tuple_transaction WHERE timestamp >= $1;
My DB CPU is 100% maxed out by this one query (nothing else is using this DB instance), latency of the query is in minutes. I'm having the same problem on RDS Postgres and RDS Aurora, on an instance that should definitely be big enough (t4g.large
).
The EXPLAIN ANALYZE doesn't look problematic. This is what I get reproducing the query now:
Aggregate (cost=8.38..8.39 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)
-> Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction (cost=0.42..8.37 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=1)
Index Cond: ("timestamp" >= '2022-03-17 11:17:34'::timestamp without time zone)
Planning Time: 0.183 ms
Execution Time: 0.035 ms
Which seems all OK to me. If I set the timestamp to be a bit more in the past though (like the 15th of March), I do get a different query plan which is much much slower (query plan here: https://discord.com/channels/844600078504951838/844600078948630559/953363762109349938), matching the slowness I'm observing on my live system.
I'm guessing that for some reason, Postgres is using the wrong query plan. I've tried playing with the --datastore-revision-fuzzing-duration
setting to reduce it to 1s, to no avail. ANALYZE
hasn't helped either.
I'd really appreciate help, it's obviously a big blocker to me and given I haven't done any specific configuration and I can reproduce on 2 databases, it's likely a SpiceDB issue? I have traces if that's helpful, but it's not telling me much apart from "this query took X minutes" which I knowJoey
03/17/2022, 11:49 AMJoey
03/17/2022, 11:50 AMJoey
03/17/2022, 11:50 AMwilliamdclt
03/17/2022, 11:51 AMJoey
03/17/2022, 11:52 AMJoey
03/17/2022, 11:52 AMwilliamdclt
03/17/2022, 11:52 AMwilliamdclt
03/17/2022, 11:52 AMJoey
03/17/2022, 11:53 AMJoey
03/17/2022, 11:53 AMwilliamdclt
03/17/2022, 11:58 AMtimestamp
parameter for the first 5 execution: that'll be fast. From the 6th execution onwards, it doesn't consider what value timestamp
actually has and always applies a generic query plan: this generic query plan is much much slower for the values of timestamp
we actually usebowersbros
03/17/2022, 11:58 AMbowersbros
03/17/2022, 11:58 AMJoey
03/17/2022, 12:00 PMJoey
03/17/2022, 12:01 PMwilliamdclt
03/17/2022, 12:01 PMJoey
03/17/2022, 12:02 PMJoey
03/17/2022, 12:03 PMand timestamp <= (some value that is a bit later than the timestamp given)
Joey
03/17/2022, 12:03 PMJoey
03/17/2022, 12:03 PMbowersbros
03/17/2022, 12:03 PMwilliamdclt
03/17/2022, 12:04 PMwilliamdclt
03/17/2022, 12:05 PMplan_cache_mode
setting that can be set to force_custom_plan
Joey
03/17/2022, 12:06 PM