we are using a DB connection pool

so its possible its preparing it

> Running setting plan_cache_mode to force_custom_plan on each DB connection in the pool seemed to do the trick. Now all queries are running <2ms. This solution feels hacky at the moment, a better one would be to avoid using a prepared statement, but I don't see that pgx package supports this

so yeah

that seems likely

@User do you have checkout of SpiceDB you're running or just the container image?

Just the container image

if a checkout, maybe try adding that setting before the query is called and seeing if it fixes the issue?

ok

Possibly, a dirty string interpolation of the timestamp param would also fix it, rather than passing it as a parameter. SQLI and all that jazz, though

as an aside, it seems that SpiceDB makes a first call to the DB to get the

now

value (L521), then computes the

timestamp

to query in go-land, then makes this

SELECT MIN(id), MAX(id) from rttx WHERE timestamp <= $1

query with the computed timestamp. It could instead query

SELECT MIN(id), MAX(id) from rttx WHERE timestamp <= now() - interval $1

, and avoid a round-trip to the DB

yeah, I'd like to avoid the SQLI possibility if there is a better way

its unfortunate there isn't a cleaner solution it seems

I'm happy to test it, but I need instructions on how to build the docker image 😬

Wait what am I saying,

docker build .

should be good enough right

can you try setting the plan_cache_mode on the db? it looks like it should be

alter database <yourdbname> set plan_cache_mode to force_custom_plan;

Done, waiting for data to come in

Yep, that does work! It's not ideal as it turns it off for all queries, but it unblocks me

😍 and I'm finally getting these perfs I was expecting!! This is such a relief!

Isn't setting this

plan_cache_mode

can be passed as a connection string?

Like this? Im purely guessing

--datastore-conn-uri="postgres://postgres:password@localhost:5432/spicedb?sslmode=disable plan_cache_mode=force_custom_plan

?

haven't tested it out but I think you can do

options='-c plan_cache_mode=force_custom_plan'

to set the value for the session when you connect (or just use a

SET

after connect)

Apparently not, only some settiings can be set (https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS)

I'll probably just add it to the

postgresql.conf

I thought its as simple as that haha. I came across this from pgx. https://github.com/jackc/pgx/issues/1101

@User how were you setting up your test? An RDS instance + a script to populate with data + some queries? I'm wondering if there's something else we could do that doesn't involve tuning pg (i.e. make sure it has the right statistics and picks a good plan on its own)

It's my staging environment, with "real" staging data and "real" staging traffic

> I'm wondering if there's something else we could do that doesn't involve tuning pg (i.e. make sure it has the right statistics and picks a good plan on its own) The thing is, the statistics are only going to be useful if PG knows what is the actual timestamp you're filtering by. And that's the whole problem: it uses a generic plan, ignoring the actual values of the parameters. So as long as we pass the

timestamp

as a param, that's going to be a problem. The only other option I see is sanitising the timestamp and injecting it into the query dynamically

which, we likely can do