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

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 :-)!