public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Subject: Re: Will hundred of thousands of this type of query cause Parsing issue
Date: Fri, 13 Sep 2024 22:08:18 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CH0PR03MB610085FA248C69CDB73494FFFE652@CH0PR03MB6100.namprd03.prod.outlook.com>
References: <CH0PR03MB610085FA248C69CDB73494FFFE652@CH0PR03MB6100.namprd03.prod.outlook.com>


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.

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Will hundred of thousands of this type of query cause Parsing issue
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox