public inbox for [email protected]
help / color / mirror / Atom feedWill hundred of thousands of this type of query cause Parsing issue
2+ messages / 2 participants
[nested] [flat]
* Will hundred of thousands of this type of query cause Parsing issue
@ 2024-09-13 15:34 Wong, Kam Fook (TR Technology) <[email protected]>
2024-09-13 20:08 ` Re: Will hundred of thousands of this type of query cause Parsing issue [email protected]
0 siblings, 1 reply; 2+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2024-09-13 15:34 UTC (permalink / raw)
To: pgsql-general <[email protected]>
Follow Postgres expert,
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?
SELECT abc, efg from DOCLOC a, COLLECTION b WHERE a.colum1 IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241, $242, $243, $244, $245, $246, $247, $248, $249, $250, $251, $252, $253, $254, $255, $256, $257, $258, $259, $260, $261, $262, $263, $264, $265, $266, $267, $268, $269, $270, $271, $272, $273, $274, $275, $276, $277, $278, $279, $280, $281, $282, $283, $284, $285, $286, $287, $288, $289, $290, $291, $292, $293, $294, $295, $296, $297, $298, $299, $300, $301, $302, $303, $304, $305, $306, $307, $308, $309, $310, $311, $312, $313, $314, $315, $316, $317, $318, $319, $320, $321, $322, $323, $324, $325, $326, $327, $328, $329, $330, $331, $332, $333, $334, $335, $336, $337, $338, $339, $340, $341, $342, $343, $344, $345, $346, $347, $348, $349, $350, $351, $352, $353, $354, $355, $356, $357, $358, $359, $360, $361, $362, $363, $364, $365, $366, $367, $368, $369, $370, $371, $372, $373, $374, $375, $376, $377, $378, $379, $380, $381, $382, $383, $384, $385, $386, $387, $388, $389, $390, $391, $392, $393, $394, $395, $396, $397, $398, $399, $400, $401, $402, $403, $404, $405, $406, $407, $408, $409, $410, $411, $412, $413, $414, $415, $416, $417, $418, $419, $420, $421, $422, $423, $424, $425, $426, $427, $428, $429, $430, $431, $432, $433, $434, $435, $436, $437, $438, $439, $440, $441, $442, $443, $444, $445, $446, $447, $448, $449, $450, $451, $452, $453, $454, $455, $456, $457, $458, $459, $460, $461, $462, $463, $464, $465, $466, $467, $468, $469, $470, $471, $472, $473, $474, $475, $476, $477, $478, $479, $480, $481, $482, $483, $484, $485, $486, $487, $488, $489, $490, $491, $492, $493, $494, $495, $496, $497, $498, $499, $500, $501, $502, $503, $504, $505, $506, $507, $508, $509, $510, $511, $512, $513, $514, $515, $516, $517, $518, $519, $520, $521, $522, $523, $524, $525, $526, $527, $528, $529, $530, $531, $532, $533, $534, $535, $536, $537, $538, $539, $540, $541, $542, $543, $544, $545, $546, $547, $548, $549, $550, $551, $552, $553, $554, $555, $556, $557, $558, $559, $560, $561, $562, $563, $564, $565, $566, $567, $568, $569, $570, $571, $572, $573, $574, $575, $576, $577, $578, $579, $580, $581, $582, $583, $584, $585, $586, $587, $588, $589, $590, $591, $592, $593, $594, $595, $596, $597, $598, $599, $600, $601, $602, $603, $604, $605, $606, $607, $608, $609, $610, $611, $612, $613, $614, $615, $616, $617, $618, $619, $620, $621, $622, $623, $624, $625, $626, $627, $628, $629, $630, $631, $632, $633, $634, $635, $636, $637, $638, $639, $640, $641, $642, $643, $644, $645, $646, $647, $648, $649, $650, $651, $652, $653, $654, $655, $656, $657, $658, $659, $660, $661, $662, $663, $664, $665, $666, $667, $668, $669, $670, $671, $672, $673, $674, $675, $676, $677, $678, $679, $680, $681, $682, $683, $684, $685, $686, $687, $688, $689, $690, $691, $692, $693, $694, $695, $696, $697, $698, $699, $700, $701, $702, $703, $704, $705, $706, $707, $708, $709, $710, $711, $712, $713, $714, $715, $716, $717, $718, $719, $720, $721, $722, $723, $724, $725, $726, $727, $728, $729, $730, $731, $732, $733, $734, $735, $736, $737, $738, $739, $740, $741, $742, $743, $744, $745, $746, $747, $748, $749, $750, $751, $752, $753, $754, $755, $756, $757, $758, $759, $760, $761, $762, $763, $764, $765, $766, $767, $768, $769, $770, $771, $772, $773, $774, $775, $776, $777, $778, $779, $780, $781, $782, $783, $784, $785, $786, $787, $788, $789, $790, $791, $792, $793, $794, $795, $796, $797, $798, $799, $800, $801, $802, $803, $804, $805, $806, $807, $808, $809, $810, $811, $812, $813, $814, $815, $816, $817, $818, $819, $820, $821, $822, $823, $824, $825, $826, $827, $828, $829, $830, $831, $832, $833, $834, $835, $836, $837, $838, $839, $840, $841, $842, $843, $844, $845, $846, $847, $848, $849, $850, $851, $852, $853, $854, $855, $856, $857, $858, $859, $860, $861, $862, $863, $864, $865, $866, $867, $868, $869, $870, $871, $872, $873, $874, $875, $876, $877, $878, $879, $880, $881, $882, $883, $884, $885, $886, $887, $888, $889, $890, $891, $892, $893, $894, $895, $896, $897, $898, $899, $900, $901, $902, $903, $904, $905, $906, $907, $908, $909, $910, $911, $912, $913, $914, $915, $916, $917, $918, $919, $920, $921, $922, $923, $924, $925, $926, $927, $928, $929, $930, $931, $932, $933, $934, $935, $936, $937, $938, $939, $940, $941, $942, $943, $944, $945, $946, $947, $948, $949, $950, $951, $952, $953, $954, $955, $956, $957, $958, $959, $960, $961, $962, $963, $964, $965, $966, $967, $968, $969, $970, $971, $972, $973, $974, $975, $976, $977, $978, $979, $980, $981, $982, $983, $984, $985, $986, $987, $988, $989, $990, $991, $992, $993, $994, $995, $996, $997, $998, $999, $1000) 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=$1002)
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Will hundred of thousands of this type of query cause Parsing issue
2024-09-13 15:34 Will hundred of thousands of this type of query cause Parsing issue Wong, Kam Fook (TR Technology) <[email protected]>
@ 2024-09-13 20:08 ` [email protected]
0 siblings, 0 replies; 2+ messages in thread
From: [email protected] @ 2024-09-13 20:08 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-09-13 20:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-13 15:34 Will hundred of thousands of this type of query cause Parsing issue Wong, Kam Fook (TR Technology) <[email protected]>
2024-09-13 20:08 ` [email protected]
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox