https://authzed.com logo
#spicedb
Title
# spicedb
w

williamdclt

03/17/2022, 11:27 AM
I'm still seeing this insanely high CPU usage for this query on postgres:
SELECT 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:
Copy code
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 know