In Postgres I have a table that I run a function against which returns the top result. This function takes around 2ms to complete without cached data, which is what I need.
I then drop PostgREST in the mix, since I need to have an HTTP API that will allow services to run this function and use its results.
When I do a curl against the PostgREST API, I get a response time of 0.29 seconds, which is 290ms and ridiculously slower
What is making PostgREST so slow?
Postgres 12 and PostgREST 7.0.1 are running on the same machine and my requests are coming from the same machine, so there should be very little network latency.
curl -d rating_min=5 -d rating_max=8 http://localhost:7045/rpc/get_json_data -w " %{time_starttransfer}\n"
> [{"json": {"name": "Friend"} }] 0.291
CREATE TABLE my_json_data (
"json" jsonb NULL,
mtime timestamp NULL,
rating float8 NULL
);
CREATE OR REPLACE FUNCTION get_json_data(rating_min float, rating_max float)
RETURNS TABLE(
"json" jsonb
) AS
$$
SELECT "json"
FROM my_json_data
WHERE rating BETWEEN rating_min and rating_max
ORDER BY rating
LIMIT 1
$$ LANGUAGE SQL IMMUTABLE;
You are comparing the query speed (in your postgresql client) to the "query speed + db connection time". i.e when you do your curl request, in addition to running the query, postgrest also needs to connect to the database, and this takes time. By default a connection is closed after 10s (look into db-pool-timeout). So this is the reason why your first call is slow, and the second one is fast (nothing to do with query cache plan). If you make a curl request (the first one) it will be slow, the second one (if made immediately after) it will be fast, then wait 10-15s and make the 3rd one, you will see that it will be slow again (since the db connection was closed). What you want is to adjust the db-pool-timeout to like 30m and all your calls will be fast (except the first one)