From: ihab sabiq <sabiq.ihab@hotmail.fr>
To: "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Subject: PrepareStatement and parameters data types
Thread-Topic: PrepareStatement and parameters data types
Thread-Index: AQHZyi4lMynpkbjX9kyRTclbgNizhQ==
Date: Tue, 8 Aug 2023 19:27:35 +0000
Message-ID: 
 <PRAP192MB16285D5564F9EFEFDAAD1AC2FE0DA@PRAP192MB1628.EURP192.PROD.OUTLOOK.COM>
Accept-Language: fr-FR, en-US
Content-Language: fr-FR
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?zYe8qVYr0obgArMM7VgBMts90ESmyMfx3KYdT1s39M0sgBp+oN+yeO0LK8xv?=
 =?us-ascii?Q?0LaJu7E3SDpQE8NVOrUXlR0rLcC7xCERzWgdI8naJeLoacRxhgSd6PGed3Bt?=
 =?us-ascii?Q?qUyGwysVgKfSnPHOjRwDKSWINKliUGcfQrOAau9AOZdC6W37DLTYT7Nf0ssD?=
 =?us-ascii?Q?M58wJc0OMQSesyTu6wSPvCUre0yAFSmn0PvViVZcm7T6VdQIEXSe+YLXyuBV?=
 =?us-ascii?Q?eGcAAzKgc9bu07wLcQ+1ajPAVwsEfWuFW7nbCnIQqWFs/hagVmDiikQvmgmI?=
 =?us-ascii?Q?rz0ukHMXqdu5YByh0zj86f+A9R8ziA7Ia1l+xEmlK0wEOBU0V/O1H/d26saL?=
 =?us-ascii?Q?g90Es9qec6CWN6ijBwW/2s93pMa6TdxasNeOtXGWgeIW2xTvL+d1SM7FmmZP?=
 =?us-ascii?Q?gnkOqvHXuIeWhmvTjJspfdzE630K+2dkMsqE8kfKKNRRygpcy9xoV9oDwqos?=
 =?us-ascii?Q?1aDLbCUTcGG5CuIVIK9crXmPib1740aGg+1Ng1iE7F70/r2dv2OBD2hl2EeY?=
 =?us-ascii?Q?8Qq+aYwasFyzSYtYQpGimdUmnG+0uYM1Fb+MWxT5eFminGNEu6J5YNnEB0Rd?=
 =?us-ascii?Q?ElvnWuW3E29EE90nZqxyqxTwAGuY9dOa25pGGDR4Q/ZLuoTpMYn07aSTpwtg?=
 =?us-ascii?Q?h57WPXSoLJ0ASg4EZa6vpOXAhq9GSoLdiXBBZTf6WYueP5C5fzG6kqrMnr/4?=
 =?us-ascii?Q?757NOdPLuxHCcmiTXxi9q4WHJCGgF/QDP8GbKXiZ5PYVQV2vK/OQ/2wfRjdw?=
 =?us-ascii?Q?FBpAKQ9j0ltxg2VIopl39OdDlPBE4Tb4AgBDKOMPJzztBOj/C0BxVqGIL3WH?=
 =?us-ascii?Q?Oz2o3+VMMaeQaAeJZWQWG+H6TUueV9/iUa9tnA0htkU2Ahd8Hn3jtVaYSw8l?=
 =?us-ascii?Q?cAKJoKeS1rupHwjfxYnhTRxWEGInBGfKXSjiE55shtWOgRZovc8W0Vqp8n7/?=
 =?us-ascii?Q?FQ/zphsH6VJ2CIuzBFJjROf6O6PaBYoqH801Xr1oPhp+9uwpv1h+2kASWoa3?=
 =?us-ascii?Q?gqB9dbZo/5Fy/SezJQcOJF4aKc5J8ewCWqPxF/5MfnzChuY9Zc3wqg5nKC1R?=
 =?us-ascii?Q?XsCY6vSxVaGg985Fvo4VRDylOr7zz5NUGshQj45snAOVJqATZy88IEDwAajp?=
 =?us-ascii?Q?sk635RsUXd+Gu6Hfx1sA5DlYTLnD1W2GEed2Vqp7+XRFepudGEeXQVTyqgeh?=
 =?us-ascii?Q?R3DPNot1q6JoXC8y+UngqPigfN24Chnd8FG84TZKw9eqDxp4ltt/zIaLbs0?=
 =?us-ascii?Q?=3D?=
Content-Type: multipart/alternative;
	boundary="_000_PRAP192MB16285D5564F9EFEFDAAD1AC2FE0DAPRAP192MB1628EURP_"
MIME-Version: 1.0
X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-fb43a.templateTenant
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: PRAP192MB1628.EURP192.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 8e670e77-6074-41a6-986f-08db98458551
X-MS-Exchange-CrossTenant-originalarrivaltime: 08 Aug 2023 19:27:35.9484
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PAWP192MB2227
List-Id: <pgsql-jdbc.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-jdbc@lists.postgresql.org>
List-Owner: <mailto:pgsql-jdbc-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-jdbc>
Archived-At: 
 <https://www.postgresql.org/message-id/PRAP192MB16285D5564F9EFEFDAAD1AC2FE0DA%40PRAP192MB1628.EURP192.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_PRAP192MB16285D5564F9EFEFDAAD1AC2FE0DAPRAP192MB1628EURP_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello,

I was doing some experimenting to try and reduce the execution time of a qu=
ery used in a PreparedStatement. To do that, I normally use the Prepare and=
 Execute statements of Postgresql. What I notice is that explicit casting h=
as a considerable performance impact. So using the good data types in the P=
repare and avoid casting gives much better performance. My questions are :

  *   Does the implementation of PreparedStatment in Postgresql driver uses=
 the Prepare and Execute
  *   If so why I cannot see the Prepare and the parameters type in the log=
s if not how can I simulate the same mechanism from the command line
  *   Is it possible to avoid explicit casting without getting the error co=
uld not determine data type of parameter

The query is bit complex on large tables but the main part is :  cast($1 as=
 text) is null or cast($2 as text) is null or ( field < cast(cast($3 as tex=
t) as date) and field > cast(cast($4 as text) as date) )

Thanks for your help.

Sabiq

--_000_PRAP192MB16285D5564F9EFEFDAAD1AC2FE0DAPRAP192MB1628EURP_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
</head>
<body>
<div><span dir=3D"auto" style=3D"font-family:-apple-system, HelveticaNeue;f=
ont-size:16px">Hello,</span></div>
<div dir=3D"auto" style=3D"font-family:-apple-system, HelveticaNeue;font-si=
ze:16px"><br>
</div>
<div dir=3D"auto" style=3D"font-family:-apple-system, HelveticaNeue;font-si=
ze:16px">I was doing some experimenting to try and reduce the execution tim=
e of a query used in a PreparedStatement. To do that, I normally use the Pr=
epare and Execute statements of Postgresql.
 What I notice is that explicit casting has a considerable performance impa=
ct. So using the good data types in the Prepare and avoid casting gives muc=
h better per<span>formance. My questions are :</span></div>
<div dir=3D"auto" style=3D"font-family:-apple-system, HelveticaNeue;font-si=
ze:16px">
<ul>
<li><span>Does the implementation of PreparedStatment in Postgresql driver =
uses the Prepare and Execute</span></li><li><span>If so why I cannot see th=
e Prepare and the parameters type in the logs if not how can I simulate the=
 same mechanism from the command line</span></li><li><span>Is it possible t=
o avoid explicit casting without getting the error&nbsp;<i>could not determ=
ine data type of parameter</i></span></li></ul>
<div dir=3D"auto">The query is bit complex on large tables but the main par=
t is :&nbsp; cast($1 as text) is null or cast($2 as text) is null or ( fiel=
d &lt; cast(cast($3 as text) as date) and&nbsp;<span style=3D"color:rgb(0, =
0, 0);font-size:medium;background-color:rgb(255, 255, 255);display:inline !=
important;line-height:1.5">field
 &gt; cast(cast($4 as text) as date)<span>&nbsp;)</span></span></div>
<div dir=3D"auto"><br>
</div>
<div dir=3D"auto">Thanks for you<span>r help.</span></div>
<div dir=3D"auto"><span><br>
</span></div>
<span dir=3D"auto"><span>Sabiq</span></span></div>
</body>
</html>

--_000_PRAP192MB16285D5564F9EFEFDAAD1AC2FE0DAPRAP192MB1628EURP_--