Re: AW: postgresql long running query

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);'

śr., 8 gru 2021 o 21:02 liam saffioti <liam(dot)saffiotti(at)gmail(dot)com> napisał(a):

> I have a psql script that runs every 15 minutes in cron.
> Thanks,
> Liam
>
> androxkentaki . <androxkentaki(at)gmail(dot)com>, 8 Ara 2021 Çar, 21:57
> tarihinde şunu yazdı:
>
>> 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
>> ------------------------------
>> *From:* liam saffioti <liam(dot)saffiotti(at)gmail(dot)com>
>> *Sent:* Wednesday, December 8, 2021 4:19:33 PM
>> *To:* Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>
>> *Cc:* pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
>> *Subject:* Re: AW: postgresql long running query
>>
>> 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
>>
>> Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>, 8 Ara 2021 Çar, 17:11 tarihinde
>> şunu yazdı:
>>
>> On 12/8/21 06:13, Dischner, Anton wrote:
>>
>>
>>
>> Do optimizer hints in PG exists?
>>
>>
>>
>> Obviously not: https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
>>
>> Yes, they do exist: http://pghintplan.osdn.jp/pg_hint_plan.html
>>
>> Packages can be downloaded here:
>> https://osdn.net/projects/pghintplan/releases/73862
>>
>> There is no package for version 14, you will have to build it from the
>> source:
>>
>> https://github.com/ossc-db/pg_hint_plan
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>
>>

--
Z wyrazami szacunku
Andrzej Gerasimuk