Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mv28B-00072x-Ox for pgsql-admin@arkaria.postgresql.org; Wed, 08 Dec 2021 18:57:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mv28A-0000PV-GK for pgsql-admin@arkaria.postgresql.org; Wed, 08 Dec 2021 18:57:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mv28A-0000PB-2f for pgsql-admin@lists.postgresql.org; Wed, 08 Dec 2021 18:57:30 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mv286-00042Z-Ob for pgsql-admin@lists.postgresql.org; Wed, 08 Dec 2021 18:57:29 +0000 Received: by mail-ed1-x52e.google.com with SMTP id y12so11323016eda.12 for ; Wed, 08 Dec 2021 10:57:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=from:to:cc:subject:thread-topic:thread-index:date:message-id :references:in-reply-to:accept-language:content-language :mime-version; bh=9ASAOPSJNanR/IUZsyG2F1sCEPk7bDh6rkBvGh0yvvI=; b=pUOhvA+iy4cQgrbTwv6Z3M/VV45E/HVGNWsrCXq3KCd7857Ajwia58WXsKlU5TZbZq b73XYeaRD0TZ7+pyEZWwr40/JIvWf/K0QY783oRfDlX4HMlflC0M0o3dZlxYk15krtuc yxWF/W4QS+GEUw7wlnCT82xX+7jZdIJHxjyqcqxsGM31hKJnss9sCIDsRjfPHS0oiCee 63DpSKki6Po8hZ9kZ52CIcocouLqr7joiUwb5sIp1zYm+lCapTAvEnFUQTxWDkKHGHce 3Tu2T3EVKdsI6BNtEQ/zfRQ3qS4qMMPSZUVHOoJ1FvKGqeNw+/ldEaEL/PZgLTvOB5T9 PFMg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:to:cc:subject:thread-topic:thread-index :date:message-id:references:in-reply-to:accept-language :content-language:mime-version; bh=9ASAOPSJNanR/IUZsyG2F1sCEPk7bDh6rkBvGh0yvvI=; b=eJ0AOWcswvCCYmXvl1+Onr2kmVeuNsRAJe4M5LpVRFn9PxQ8Zz4aYoMb8WkHF5iObC szyqudMa4U3VHnh9KPHOTW2aTvbOGZV9iCVgui9Y3sZYDzxF+W4XTv1kgrKEN9yLuSmV Jp5KNrtcpGMpb5syrtb8HeIFpThobojL/YCIusfTYfPl7v14qTIJPAwE6NKYGl9VyNM6 T8PI0BAjmuY7Dh1ynnIRW+wMxLKdHZ9cFBhmblHQD7n6R3jXtMKRt3rnzYNHbpmp6wWA ILKIathnbiEQeaea1xaw/vKJLM0F5QwALRVvU/tSfzm85jgy2nBD9r59Ou/WlnQFXNxq nVyw== X-Gm-Message-State: AOAM5331GmWWncJW47DEeZa/SQk14YhAFKuMWsSUXCAZQUAM5/7lAt2+ HeqpHuNhfiOBKu7gUMjaYWxOKsdDaPkbWg== X-Google-Smtp-Source: ABdhPJzQwtGxXUaspK9fYWJRHJlkQIQNG8eXrd74M09CHioVvI81DwVnj8i21SqICYdF/4oLQCaqUA== X-Received: by 2002:a17:906:7ccf:: with SMTP id h15mr9636062ejp.386.1638989844834; Wed, 08 Dec 2021 10:57:24 -0800 (PST) Received: from AM0PR10MB3185.EURPRD10.PROD.OUTLOOK.COM ([2603:1026:207:182::5]) by smtp.gmail.com with ESMTPSA id qk40sm1826753ejc.2.2021.12.08.10.57.24 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 08 Dec 2021 10:57:24 -0800 (PST) From: "androxkentaki ." To: liam saffioti , Mladen Gogala CC: pgsql-admin Subject: Re: AW: postgresql long running query Thread-Topic: AW: postgresql long running query Thread-Index: AWdrQ0Ja0QziFGa/RGY/uxEhi4qgCeD57mIAgAAZzoCABGXRAIADKF4AgAAsewCAADGpAIAAEvmAgAA8hDA= X-MS-Exchange-MessageSentRepresentingType: 1 Date: Wed, 8 Dec 2021 18:57:22 +0000 Message-ID: References: <8718fe01e83140b18f7bc8777469c44d@MITMB5.helios.med.uni-muenchen.de> <52ae89c7f1564de085f43510f39982f9@MITMB5.helios.med.uni-muenchen.de> <07b023154ae14171a05a802e4dbe1cb2@MITMB5.helios.med.uni-muenchen.de> In-Reply-To: Accept-Language: pl-PL, en-US Content-Language: pl-PL X-MS-Has-Attach: X-MS-Exchange-Organization-SCL: -1 X-MS-TNEF-Correlator: X-MS-Exchange-Organization-RecordReviewCfmType: 0 Content-Type: multipart/alternative; boundary="_000_AM0PR10MB31859B492561952578F7C16CFD6F9AM0PR10MB3185EURP_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM0PR10MB31859B492561952578F7C16CFD6F9AM0PR10MB3185EURP_ Content-Type: text/plain; charset="iso-8859-3" Content-Transfer-Encoding: quoted-printable 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 Sent: Wednesday, December 8, 2021 4:19:33 PM To: Mladen Gogala Cc: pgsql-admin Subject: Re: AW: postgresql long running query Hi everyone, Thank you for your help. But my query is not always slow. It works fast und= er normal conditions, but in a way that I don't understand it works very sl= ow at some times. I think there may be a different reason why a query that takes 800ms, in ge= neral, takes 18 minutes at a time. Thanks a lot Liam Mladen Gogala >, 8 = Ara 2021 =C7ar, 17:11 tarihinde =BAunu yazd=B9: 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/relea= ses/73862 There is no package for version 14, you will have to build it from the sour= ce: https://github.com/ossc-db/pg_hint_plan -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --_000_AM0PR10MB31859B492561952578F7C16CFD6F9AM0PR10MB3185EURP_ Content-Type: text/html; charset="iso-8859-3" Content-Transfer-Encoding: quoted-printable
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 <lia= m.saffiotti@gmail.com>
Sent: Wednesday, December 8, 2021 4:19:33 PM
To: Mladen Gogala <gogala.mladen@gmail.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: AW: postgresql long running query
 
Hi everyone,

Thank you for your help. But my query is not always slow. It work= s fast under normal conditions, but in a way that I don't understand it wor= ks 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@gmail.com>, 8 Ara 2021 =C7ar= , 17:11 tarihinde =BAunu yazd=B9:
On 12/8/21 06:13, Dischner, Anton wrote:

 

Do optimizer hints in PG exists?<= u>

 

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 s= ource:

ht= tps://github.com/ossc-db/pg_hint_plan

--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbw=
hisperer.wordpress.com
--_000_AM0PR10MB31859B492561952578F7C16CFD6F9AM0PR10MB3185EURP_--