Thread

1. Will hundred of thousands of this type of query cause Parsing issue

   Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com> — 2024-09-13T15:34:55Z

   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
   

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

   David Mullineux <dmullx@gmail.com> — 2024-09-13T16:58:43Z

   I would usually try to avoid such long IN causes. Why not try this ......
   Create a temp table of 1 column. Bulk insert all your IDs into that table.
   Then change your query to join to the temp table?  This also has the
   advantage of working for 1000s of values.
   
   On Fri, 13 Sept 2024, 16:35 Wong, Kam Fook (TR Technology), <
   kamfook.wong@thomsonreuters.com> wrote:
   
   > 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
   >
   

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

   Greg Sabino Mullane <htamfids@gmail.com> — 2024-09-13T19:04:40Z

   On Fri, Sep 13, 2024 at 11:35 AM Wong, Kam Fook (TR Technology) <
   kamfook.wong@thomsonreuters.com> wrote:
   
   > 1) Where does query parsing occur?
   >
   >
   Always on the server side, although your driver may do something as well.
   
   2) Will this cause extra parsing to the posgress DB?
   >
   
   Yes
   
   
   >   Any pg system table to measure parsing?
   >
   
   No
   
   You want to send an array of values to the same query, so it can be
   prepared once, like so:
   
   SELECT abc, efg
   FROM docloc a
   JOIN collection b USING (collection_name)
   WHERE a.column1 = ANY($1)
   AND a.stage_id = (
     select max(stage_id) from collection_pit c
     where c.collection_name = a.collection_name
     and c.pid_id < $2 and c.stage_code = $3
   );
   
   Then you can always pass in three arguments, the first being an array of
   all the column1 values you want.
   
   You might also want to get familiar with plan_cache_mode:
   https://www.postgresql.org/docs/current/sql-prepare.html
   
   Cheers,
   Greg
   

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

   shammat@gmx.net — 2024-09-13T20:08:18Z

   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.