Re: AW: postgresql long running query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux