Message-ID: <c9ef8e6f-3486-487a-9b8b-18881df47797@gmx.net>
Date: Fri, 13 Sep 2024 22:08:18 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Will hundred of thousands of this type of query cause Parsing
 issue
To: pgsql-general@lists.postgresql.org
References: <CH0PR03MB610085FA248C69CDB73494FFFE652@CH0PR03MB6100.namprd03.prod.outlook.com>
Content-Language: de-DE, en-US
From: shammat@gmx.net
In-Reply-To: <CH0PR03MB610085FA248C69CDB73494FFFE652@CH0PR03MB6100.namprd03.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:e+OXi9sNn5Y=;adWwcrE71MU4hl/w/Rk+bu6RFF5
 gJvH5l/r5SpCqrZntcr48YYjbv7T1btcQgmBgSqtzRJ8wXmTNdgKM/UijD6lGpgV0h9ecUktE
 WkdCdnBMvTISph9IMM5qkYu8I49CHupHFnfjYp/puHA2sYXmHFy+mbKjjJGHdlzTeC6IJXadC
 G8RLwex1G3q/QCRzFAe3J9S74Z1Q4gbO3xCqXNP6R8SzyCbJwU9ZSpDq5o5SxtFScETNk/Mgz
 c/oLXRSlpUs/98U/C0pGzkSIPei5fxIz9gbihgwIeJQNzZh/egz8oNZbEWq15P7kt+5VFd9Se
 wLcPj4rhhWTv9q8qtpRa3Qm7kGnDLEoa3ujjf1KNgkD4i/dRjCtAeZT5yQv2pe+BrvCN6tRjF
 rcdKv2lHvbom3uQcpkgMfOZnGFcj8cGYsRYlEuiPKM8/UyNfMwAA1QU+6QkWkSpNj9N7IP/P2
 xHpKRC7AKy4FY5O5pHsiz1+srchspjyMKWpInITV3JWTe98HtDYTYsxODhwOFMETiD+NFrTQS
 dBWGuMCp+KX92kPUJHbyMom9tUmfjl0zDgQfdQM3vLo+MR7/lKmUu8uLz0WVIEKH5MbemYq9G
 3hJRwYnHaDjGmf17Fe3tsMSegG1RJ8R0kH83Ul49NWTNP5TmA7vlJHJSlL863Kb1jQXeGI3l3
 wLWv6UV8lZMcxi1mXBxgDif8+iXgb1gsgT80lI5a6mQstN/m1l1KxG/8iPxrZ1RvbrzMLO+fY
 BurQbWu9Fk9ny8KYKyFzCJ5eR9/VmGKsqB2HE0gxyewClUevSQoVs4NYdJti6OE+mERO5OYX5
 mXQvL1TUThDDYGXg6VgGub1A==
Archived-At: <https://www.postgresql.org/message-id/c9ef8e6f-3486-487a-9b8b-18881df47797%40gmx.net>
Precedence: bulk


Am 13.09.24 um 17:34 schrieb Wong, Kam Fook (TR Technology):
> We have a flavor of this type of query with long in-list/bind
> variables (see below).  We notice that some of the bind variables
> come in as 0 which causes the optimizer to choose to full scan two of
> the following 3 tables.  One thought to fix a full table scan is to
> chop off the not-needed bind variables (proven to work after some
> tests).  But my other worry is will cause parsing issues because the
> app will be executing > 100k/sec with this type of query.
>
> I am an Oracle DBA, and this change for sure will generate a
> different query id.  Which in turn generates tons of extra parsing to
> the DB because all soft and hard parsing occurs at the DB level.  But
> my understanding for Postgres is parsing occurs at the client jdbc
> level.  Am I understanding this correctly?
>
> In summary/my concern:
>
> 1) Where does query parsing occur?
> 2) Will this cause extra parsing to the posgress DB?=C2=A0 Any pg system=
 table to measure parsing?
>

You can simplify the query to a single parameter by passing the list of va=
lues as an array:

SELECT  abc, efg
from DOCLOC a,
      COLLECTION b
WHERE a.colum1 =3D ANY($1)
   AND a.COLLECTION_NAME=3Db.DOCLOC.COLLECTION_NAME
   AND a.DOCLOC.STAGE_ID=3D(SELECT MAX (STAGE_ID)
                          FROM COLLECTION_PIT
                          WHERE COLLECTION_PIT.COLLECTION_NAME=3Da.COLLECT=
ION_NAME
                          AND COLLECTION_PIT.PIT_ID<=3D$1001 AND COLLECTIO=
N_PIT.STAGE_CODE=3D$2)

You can then pass the array using PreparedStatement.setArray()

This has the additional advantage that you don't need to build the query d=
ynamically
and there is only a single statement to be parsed. I don't think Postgres =
distinguishes
between soft and hard parses as it doesn't cache plans as aggressively as =
Oracle.