Re: AW: postgresql long running query

[androxkentaki <androxkentaki@xxxxxxxxx>]

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.saffiotti@xxxxxxxxx> napisał(a):

I have a psql script that runs every 15 minutes in cron.

Thanks,

Liam

androxkentaki . <androxkentaki@xxxxxxxxx>, 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.saffiotti@xxxxxxxxx>
Sent: Wednesday, December 8, 2021 4:19:33 PM
To: Mladen Gogala <gogala.mladen@xxxxxxxxx>
Cc: pgsql-admin <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
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.mladen@xxxxxxxxx>, 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-1217
https://dbwhisperer.wordpress.com

--

Z wyrazami szacunku
Andrzej Gerasimuk