williamdclt03/17/2022, 11:27 AM
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 (
SELECT MIN(id), MAX(id) FROM relation_tuple_transaction WHERE timestamp >= $1;
). The EXPLAIN ANALYZE doesn't look problematic. This is what I get reproducing the query now:
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
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
setting to reduce it to 1s, to no avail.
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