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.
