Re: Will hundred of thousands of this type of query cause Parsing issue

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?  Any pg system table to measure parsing?
>

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

SELECT abc, efg
from DOCLOC a,
COLLECTION b
WHERE a.colum1 = ANY($1)
AND a.COLLECTION_NAME=b.DOCLOC.COLLECTION_NAME
AND a.DOCLOC.STAGE_ID=(SELECT MAX (STAGE_ID)
FROM COLLECTION_PIT
WHERE COLLECTION_PIT.COLLECTION_NAME=a.COLLECTION_NAME
AND COLLECTION_PIT.PIT_ID<=$1001 AND COLLECTION_PIT.STAGE_CODE=$2)

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

This has the additional advantage that you don't need to build the query dynamically
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.