do You have whole SELECT SQL statement in script?
May be try to prepare function on Your database with Your SQL function and call it in cron via psql script
CREATE OR REPLACE FUNCTION public.cron_job(
integer)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
insert into MYTABLE
SELECT nspname AS schema_name, relname AS table_name, pg_total_relation_size(C.oid) AS table_size FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND pg_total_relation_size(C.oid) > 100000000 AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 30;
END;
$BODY$;
ALTER FUNCTION public.cron_job(integer)
OWNER TO postgres;
and in psql:
sudo -u postgres psql --dbname MYDB -c 'SELECT public.cron_job(1);'
I have a psql script that runs every 15 minutes in cron.
Thanks,
Liam
Can you tell us how do you run query at night?
IS it via Odbc? Or direct by pgsql script in cron?
Pozdrawiam
Andrzej Gerasimuk
Hi everyone,
Thank you for your help. But my query is not always slow. It works fast under normal conditions, but in a way that I don't understand it works very slow at some times.
I think there may be a different reason why a query that takes 800ms, in general, takes 18 minutes at a time.
Thanks a lot
Liam
--
Z wyrazami szacunku
Andrzej Gerasimuk