Postgres query slow, shows long running active queries, statement_timeout does not work

Question

I have a Postgres database that's accessed through a node.js api using a mobile react native app. Don't thing the code stack specifics matter, but essentially there are queries in the psql db that stick around as status = 'active' for days that are slowing the whole database way down. Using the following to check:

SELECT
 pid,
 now() - pg_stat_activity.query_start AS duration,
 query,
 state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Manually killing the active queries with

SELECT pg_cancel_backend(__pid__)

fixes the issue and gets the database back to normal speed. But, of course we want to make sure this doesn't necessarily happen intermittently and slows down our app for all users.

Another thing to mention is that I've attempted to put a time limit on the active queries with statement_timeout but I'm not sure I'm doing this correctly. Testing it with...

SET statement_timeout = 1;
SELECT * from "table_name";

does not work. That is... the SELECT statement above takes 400 milliseconds and doesn't terminate. Seems that I don't have this procedure right. The statement does work on

SET statement_timeout = 10000;
SELECT pg_sleep(15);

with it canceling the second statement. Does that mean that statement_timeout only works on idle processes and not execution time?

The specifics of the query (including just anything out of the ordinary, everything else is normal 'where' statements)

1. It's a select statement joining three tables together: two of them are normal, one is a zipcodes table with 40k+ rows indexed on the zipcode column.
2. There is a CASE statement in the SELECT piece (before where), like such

(CASE
 WHEN
 (SELECT earth_distance(
 ll_to_earth(z.lat, z.lng),
 ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
 (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 5
 THEN 0
 WHEN
 (SELECT earth_distance(
 ll_to_earth(z.lat, z.lng),
 ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
 (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 10
 THEN 1
 WHEN
 (SELECT earth_distance(
 ll_to_earth(z.lat, z.lng),
 ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
 (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 20
 THEN 2
 ELSE (SELECT earth_distance(
 ll_to_earth(z.lat, z.lng),
 ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
 (SELECT lng from zipcodes where zipcode = '<zipcode>'))))
 END )
 AS distance

Yes, this definitely seems like it would take some time, however it does not for most users. And still, why would this query show as 'active' for days. For example, if it couldn't find the zipcode in the zipcodes table it would either not return anything or error out.

Any and all help is greatly appreciated! Even if you see anything out of place please feel free to comment! Forgive me for any simple things I missed :-)!

Answer 1

SET statement_timeout = 1;
SELECT * from "table_name";

does not work. That is... the SELECT statement above takes 400 milliseconds and doesn't terminate. Seems that I don't have this procedure right.

I don't know what else might be going on in your system, but this definitely does work for me as long as "table_name" is large enough to take more than 1 millisecond. Are you issuing this from 'psql'? Or if from something else, then what is that? If you can't trust that your commands are actually getting sent to the database, or that any errors are reliably getting sent back, that is going to make it hard to accomplish anything else.

Answer 2

If you change a parameter's value using the SQL statement SET, the value is only changes for the current database session. It does not affect statements running in concurrent database sessions.

You need to either edit postgresql.conf or use ALTER SYSTEM to change the parameter in a configuration file and reload (not restart) PostgreSQL to change the parameter globally.