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 1leKLF-0001NY-Dh for pgsql-performance@arkaria.postgresql.org; Wed, 05 May 2021 16:25:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1leKLE-0002z7-CC for pgsql-performance@arkaria.postgresql.org; Wed, 05 May 2021 16:25:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1leJLJ-0008VB-DL for pgsql-performance@lists.postgresql.org; Wed, 05 May 2021 15:21:41 +0000 Received: from sonic306-21.consmr.mail.ne1.yahoo.com ([66.163.189.83]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1leJLF-0001ga-5n for pgsql-performance@postgresql.org; Wed, 05 May 2021 15:21:39 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1620228095; bh=KPCC77WvJ6bYWMtps9YaXNlZ1fKP9IFawOrdNIhTDr0=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=uI05FZ2tLpkudOK7V2QmVBlYBOQ5jz7mSoUQf0R/gMirn/vA2YeG3CmR97anD49hhP/qemAC/FWLpZRbm3Orsjvn7DjLSSFQWXu9fwF9qIU7j0dIL0YCuZZlmEfyksk8atiJFmtMd/vpE+wfV7TPP6w4LYegelRVf38PlKOQ46dA8AHf/Ye275/9nblqQpVZsEJ/vwnBnDBcPdCs3WRUvVhzxVVntDOpPk0nIC2als5BDgYlZwF5qmr+/XtdprG9cHUrtYcF3IgIbgR6Hy/g+NpGLjUy7fLQOFigCdiAFPY4x3tkZvEsxQwJWTUENkFnl2vzG0n2qZ/MGO71FTlSdw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1620228095; bh=jLSbNnJaSWYXz0V1LxeAuwo6XC+YNh074d4cOtK8yRk=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=ejkDVVijdGMovL5ate7CcTLOKKG6I2S2nX47Q4Nnf7GzRpuZwFkeSLwoj5TMQLccc5WTvip2ctvDM5GJUMetgoLV3VY+l3eXSBpe6BSIdxFDG0XMAUnX4zSG26wnXQkVmRO7wO6RjTSqC5930PfC6bJ12XX4EpJ6BHDZhcW007bRcmlfnomSok6+NDiiCErOf00O2WcJYXdL2qpL+xdDS0FW7+57Np0v1ghp2XFKFy0gUzmnh0PCrTz6VfXzs34XvK+UcHowHBq5qNrUQ6cQC/OX+hwV8vhIuZ0ttImQenTZ2ZxJE2CvdB2NMr+1OTIaF9p8nKEZLMh/kuKfL8pQew== X-YMail-OSG: .tgVdIAVM1lEuCFxfp1KnzvlSE5BWb.K207.vRg2NKDlxu5GKncTErAakYTPMr2 z3F1JXl2obhHzgFKuvrMy9euHKD5_KckRZf5JCzjB7VF1XNRI03YHrwsD0vomyme_IMzqgUnNU7L qQHQA8Hgo7T0i56iVNHh_juL_IHShUZu9PmJzAVKOJ0fwUFzEQF4NkBrFYzi8WhIZMGmbVCJUpy4 VtH69scGRXPftsOoCfYnV6MzUxK9BSFbKEi52aMMdGq36_k.SliLFHLG5QvvJ43nPSYBYUa5VuYr ..SKF4iT4SNiQbr.za2SzFKhZrmBPSR9Xix_Cjkgls73o6z3UTYAiJ1pklOJ1Or9QD8ZckzZSTPE qUfwbSvEpP3sUjCykRT.YtfXH6OSyvO6D.uqmuSxCm8JewlQS7PY5CZ_hA65_g5C_RLakrdognIH .tZDsolv9r.J8hNw3s.UKKv9MZ.4oVilFGJ_pNcSyadxZQI5E10Txj5zXZLOxtyaxlV5Bb58Uvnf sj8pFJ5J9bh1H0kEUQag1_2at_oPCEPFEz7YFl6_Ejaaf8YVPdyTantcUuFz9QmKA9OkktLgjKA6 Una4MZrhQBkVBrxvucRFzWR1WhRY95kKAE2o0jTiRVtk54mbxkvGJc4ZP.1AW3PdnQ64hhioQQvQ TEtKwY04bU4jCJpf4kQCMo7YWT.fcCc2c9XzWd.k5iph4.efDg_MTZaF6PmdHkDs_urFRSMFGlyf rQh26kIVRn2EBrWz89.YlgWxhDQBm._X5Zd2vr1D4th64Yp2Y5Fr67jL1SnLv8TVpcZvlneybNNc S9I5kbr7ihsLfa_LtP7HDz.ITciTFhgd1r5.6mtNFIkRSO34Pk1qqK3o1r3Dj5gFjVkjuHtVruiZ NjEPRqkozywbgsVO9XQhiXO_VNKNuc6_a5m7iMvWX8l9nh0upw.BIumA5Wx7.jmFLC85WnssK6bc tR6jAChD4sS3660W.WgarS7rSuplAsiN0uBcDzTCgdhuay0rDDSuUsRu5._HHbxHINP.kcrS85nO ck89P0q20LS6fydVZbJER2KH5Zc15XL7_ydx2DREJ6wSsrpzM_uE1PWDjGQY9TlU17URfpLTy4xz rptG3Oi.i56vPW0hcVw2CX_IVth2uHwFE5.kXNGLS6lhbvDuW0EwUX.KgjtUuroLU0zbFq1henDU VxD5AyWU1yUTCDyFo8LMXsZuEziwp5MeoQJD1VCzrFe1Vk577qa9rV4enk5sGUjoqoH7KZaTEBqR m9VV_oavUXVw0Euv_78oXomEWWwaJZThemPMj6SS4Jp5I0Hx3DS1bTHIoGi8Sny2wkgs8440Vnv3 lPJcQlOofThBn4eB2Dpk8b2LGPHxo_l5Y.WN8qmkxJnFrcSclTiyB7_t44DZj_jRTtdihy6S.bf5 nupehHB.7TouzJkS5ZpggehGgRSteCpBLrH4tDMM8Q6x9Rt6R4ygIRTN4Dxu.tUx0aFQlkn_GL50 LJs9NFNRkJVOrDAcqR7dGMtLGUbUVtGw2drx6oEpzVqp29c2.jHHKOGdsprFBxidPFA1QcTeq3Uc 9EuSWBrmJM8nae2hNgDZZXH5sr3.dWUguRZuoy6RT7U7690ox_I0MJ4ynEESO8IL0mah6fvVmIfP ep6juLyEiKDw00v6A72ts.VjgMP1hKZTuAQRZlxYu7EkJfNyUHcI151t0OfbL9sThlH8PQLnj_yl 2ontNYJjFuiGwbBwaJkyf3VrdRHsgzRRFXFtW.yjBHjB4AmK_a4g48zZGyP7uwm3cwnopFGoTHDc Ja2dF6j0TbDez9118BYT4Z2._EKA_WWxDMQBLbTq.ORL6ir3jpSYtHrMNrrjNd0Du6Pj2HQbetX6 PDfWFJYTirF5Ug52pPCw93eq1jADaZHXraKPMkJ8TFR0JQIhKY3D7QwtuhVvpe_zJJFUxdlpmZff JoH.Q5v4NThN3bN61r9yCBaoggwz0Bw6x_FRj_IzFYP.a9XtwnNkJ3.eQvgi4__kJUq78oo3LnUI IRqgHb7TLOgSFKMNqARTTK9.TgyLuWX0_R07c5m_OV10SLcfHMI8c6GjZGjXkYXzzo5BT_AT9cqV 8cLnKfaNs1RNHbZAgyYTBtOULwO.igqW06Cc1vC7oIIhl.lPtWT8Tvac3XAHkthHi6jzS_f8RPsH 1Yf_HX_t4qqQ5e3CB8nWiHLshcl_6Dyac02Lt75emRAc8CnQk3ksssko4OKnFqbHRFTaqZ4ti.EY 1G4osO28RT5StTECbTfgNzfPshCbnOoB.VIzOdQQxqvessSJRk0Yjtog1IQD07n6gymG.XXbAOzq 997TnjsVfdhGQ5_5GMOOpCHjyq7VSx5beLREzDUjrX_8M3DORm21blzYPPfPwAINT401ZFOw1Znk gns1mOJ8XJPU8s5KO3o_N6lGkQ_r5N2cx1exnnv0nN0xq.j.mUXLdSTwimI_diOsL4pgvuan_Hn5 brAfISjVfCZJ4c0uoPcQ3cYrIb.sETp8RxR.yUsrC6rvZEBeLRcHLr7.YOPCmH9lNygMhjDiP342 d X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ne1.yahoo.com with HTTP; Wed, 5 May 2021 15:21:35 +0000 Date: Wed, 5 May 2021 15:21:30 +0000 (UTC) From: Alex To: David Rowley Cc: Justin Pryzby , pgsql-performance Message-ID: <416718045.765440.1620228090971@mail.yahoo.com> In-Reply-To: References: <3f8c88d8a6ef9.d82fa04cf610d.ref@yahoo.com> <3f8c88d8a6ef9.d82fa04cf610d@yahoo.com> <20210503201811.GA27406@telsasoft.com> <49daf090ac5b1.96e1ada6021ee@yahoo.com> Subject: Re: 15x slower PreparedStatement vs raw query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_765439_1277861844.1620228090969" X-Mailer: WebService/1.1.18231 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:88.0) Gecko/20100101 Firefox/88.0 Content-Length: 5481 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_765439_1277861844.1620228090969 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable This is exactly my issue. Using raw query, planning takes 22ms (custom plan), but using PreparedState= ment planning takes 11ms (generic plan).It choose the faster generic plan 1= 1ms, wining 11ms faster than custom plan, but loosing 14seconds!!! to execu= tion... The auto choose algorithm should be changed to include execution time in th= e decision. On Wednesday, May 5, 2021, 9:57:20 AM GMT+3, David Rowley wrote: =20 =20 On Tue, 4 May 2021 at 22:05, Alex wrote: > Shouldn't this process be automatic based on some heuristics? When plan_cache_mode is set to "auto", then the decision to use a generic or custom plan is cost-based. See [1]. There's a fairly crude method there for estimating the effort required to replan the query. The remainder is based on the average cost of the previous custom plans + estimated planning effort vs cost of the generic plan.=C2=A0 The cheaper one wins. Certainly, what's there is far from perfect.=C2=A0 There are various problems with it.=C2=A0 The estimated planning cost is pretty crude and could do with an overhaul.=C2=A0 There are also issues with the plan costs not being true to the cost of the query.=C2=A0 One problem there is that run-time partition pruning is not costed into the plan.=C2=A0 This might cause choose_custom_plan() to pick a custom plan when a generic one with run-time pruning might have been better. In order to get a better idea of where things are going wrong for you, we'd need to see the EXPLAIN ANALYZE output for both the custom and the generic plan. David [1] https://github.com/postgres/postgres/blob/master/src/backend/utils/cach= e/plancache.c#L1019 =20 ------=_Part_765439_1277861844.1620228090969 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
This is exactly my issue.

Using raw query, planning takes 22ms (custom plan), but usi= ng PreparedStatement planning takes 11ms (generic plan).
It choose the faster generic plan 11ms, win= ing 11ms faster than custom plan, but loosing 14seconds!!! to execution...<= /span>

<= div dir=3D"ltr" data-setdir=3D"false">The auto choose algorithm should be c= hanged to include execution time in the decision.

=20
=20
On Wednesday, May 5, 2021, 9:57:20 AM GMT+3, David Rowl= ey <dgrowleyml@gmail.com> wrote:


On Tue, 4 May 2021 at 22:05, Alex <= ;cdalxndr@yahoo.com> wrote:
> S= houldn't this process be automatic based on some heuristics?

When plan_cache_mode is set to "auto", then the deci= sion to use a
generic or custom plan is cost-based. See [= 1]. There's a fairly crude
method there for estimating th= e effort required to replan the query.
The remainder is b= ased on the average cost of the previous custom
plans + e= stimated planning effort vs cost of the generic plan.  The
cheaper one wins.

Certainly, wh= at's there is far from perfect.  There are various
p= roblems with it.  The estimated planning cost is pretty crude and
could do with an overhaul.  There are also issues with = the plan costs
not being true to the cost of the query.&n= bsp; One problem there is that
run-time partition pruning= is not costed into the plan.  This might
cause choo= se_custom_plan() to pick a custom plan when a generic one
with run-time pruning might have been better.

In order to get a better idea of where things are going wrong for= you,
we'd need to see the EXPLAIN ANALYZE output for bot= h the custom and
the generic plan.

David


[1]
https://github.com/postgres/postgres/blob/master/= src/backend/utils/cache/plancache.c#L1019

------=_Part_765439_1277861844.1620228090969--