8 ms·

Working around a case where the Postgres planner is “not very smart”

65 points by kmdupree 5y ago

fabian2k 5y ago
That's an interesting limitation I didn't know. Though I haven't really relied on index-only scans much so far, the limitations regarding the visibility map seemed a bit too hard to judge to me. I assume that this works usually, but it's always a bit intimidating when a feature talks about limitations in DB internal data structures you didn't even know existed. What surprises me here a bit is that this only provides a factor of two improvement. I would have expected the index to be much smaller than the table, though they include a bunch of columns in the index. At that ratio I'd be a bit worried about consuming a serious amount of space for that speedup, but that's impossible to judge without knowing the details. And if the performance is important enough here of course this is still worth it even if the index is large.
- kmdupree 5y ago
  Thanks for reading! I hear you on visibility maps being intimidating. In practice, I haven't seen any cases where visibility map issues have prevented an index-only scan. But we did initially think that the visibility map was to blame for what we were seeing! RE space of the index: it cost us about 1% of the free disk space on our workers. It was worth it for this particular feature.
mst 5y ago
I confess that 'type' sounds to me like the sort of thing that's going to appear in a WHERE clause often enough that I might as well make an enum for it and materialise that out on insert whether by trigger or otherwise. Neat trick for the meantime though, I'm sure making a schema change like the one I'm thinking of would be a colossal pain in the buttocks if decided late in the run up to shipping a feature.
- fabian2k 5y ago
  I'm a huge, huge fan of JSONB columns in Postgres, but I'd agree here. Anything that is consistent and important enough should be pulled out of JSON and put into a real column. Postgres has some impressive functionality to query JSON, but it's not the same as with real columns and you run into performance issues much, much quicker.
  - asah 5y ago
    2c: in most cases, a Postgres index is (performance wise) identical to a "real column" just without the name, and indeed you have more control over the data structure for an index, and indexes can include lossy compression (e.g. partial indexes) unlike a real column... (obviously, the challenge is getting pg to use your index for a given query... for that, I like to use VIEWs to help query authors write queries that use the indexes)
    - saurik 5y ago
      Yeah. This wasn't the case a while back--PostgreSQL was missing support for what I think people call covered indexes, and so would only use the index to filter a set of candidate rows and then still had to go digging around in the real tuple to verify it matched--but I am pretty sure they changed that a while back.
- kmdupree 5y ago
  You're exactly right that our schema could be better and that it's non-trivial to execute on a schema change, especially because we actually run a distributed Postgres cluster via Citus AND we use a special sharding method that we manage manually. We actually just started working towards how we might do a schema change for the 1+ million shards in our cluster. Hopefully, we'll be able to write up some learnings on the schema change after its done. :)
  - mst 5y ago
    Right, your current solution is what I'd classify as an "expedient field hack" if I were the one implementing it, and I do mean that positively :D I look forwards to comparing notes when that post comes out.
jve 5y ago
I have an experience with MS SQL Server, so can only comment about that. And regarding performance, mainly with old 2008R2 version. The query planner isn't very clever at times. "At times" actually makes it worse, because performance bugs surface sometimes. Anyways, the result is multiple orders of magnitude difference. What takes 0.3-2ms suddenly takes hundreds of milliseconds or even seconds to complete. Multiply that with millions of execution count and there is a problem. Sometimes SQL Server WILL NOT CHOOSE covering index (with many include columns), because it also evaluates index size. And if SQL thinks that some seek on clustering index is specific enough for parameter value A, then it is disaster with parameter B. Good thing SQL server features plan guides, where you can tell server which index to use or provide other hints. Saves the world when dealing with 3rd party applications. The stuff you have to do deal with your db grows as row count grows. If configured well, can support billions of rows, as we see from this post.
- shock-value 5y ago
  > Good thing SQL server features plan guides If only Postgres maintainers got this message.
  - protomyth 5y ago
    I remember working on a Sybase 11.5 database that basically required forcing of indexes for many tables because the query planner would always go off the rails. We had to run a script in production that would print out the query plan of the currently active queries that were taking forever. Without being able to force indexes, I have no idea how we'd of fixed the problem.
    - shock-value 5y ago
      I like Postgres but this aspect is one of its shortcomings. In Postgres you usually have to rework your query in weird ways and hope that it's possible to get the optimizer to make the right choice. Even then, due to its usage of statistics, it could switch plans at any time as your data grows/changes.
  - 5y ago
bluedino 5y ago
In articles like these, I wish they would go a little deeper and says why the system does what it does. Perhaps the default behavior is geared towards low-memory servers, or avoids disk writes, or the data is usually expected to be of a certain type. Perhaps the system "isn't very smart" like it says, or it was originally intended for other purposes.
- fabian2k 5y ago
  I don't know the internals here, but I think I can make a plausible guess here. The index-only scan is generally triggered only when every column in your query is contained in the relevant index. In this case the index is a functional index, so it's not only plain columns in there. The function needs the data column, but the functional index already stores the result of the function. To the query planner it looks like the query needs the data column, while it actually doesn't need it. This is a bit of an uncommon case that would need special handling in the query planner. So it's not that odd that it is not implemented yet, though I would suspect that it'll be added at some point.
- talos 5y ago
  I definitely agree with you, but in this case the Postgres team itself frames the suboptimal behavior as such: > However, PostgreSQL's planner is currently not very smart about such cases. (From https://www.postgresql.org/docs/10/indexes-index-only-scans.html https://www.postgresql.org/docs/10/indexes-index-only-scans....) I suspect this is just a rough edge. Many types of functions may not be consistent, and therefore would need to be re-executed on the original data. Perhaps some of the planner functionality here predates function notation like `IMMUTABLE`.
orthoxerox 5y ago
And yet Postgres still refuses to add hints. I'd rather deal with a nonexistent query optimizer, like the one in Clickhouse, than with an insufficiently smart one that I can't control.
- bmurphy1976 5y ago
  I haven't done much database work in recent years, but this was always one of my frustrations. I'd take predictability over max performance any day.
- mike_hock 5y ago
  A hint won't make the query planner use an index column it doesn't know it can use, though.
  - lazide 5y ago
    It does tell the query planner it can use it though.
    - deleted 5y ago
      [deleted]
    - SpicyLemonZest 5y ago
      "Doesn't know it can use" in this context means the system probably doesn't know how to use it, even if a hint suggests it should.
      - lazide 5y ago
        Many times it's not that, or specifically that type of behavior doesn't cause the weird 'something randomly blows up' type problems. It's that it is using index size vs on disk size or something for specific query values and deciding to do something kind of dumb, like a full range scan, because it isn't taking into account the disk data is on... well disk, and the index is already loaded into memory because of other queries. Semi-locking it to known good indexes or whatever, or at least overriding it's attempt to be smart, helps usually in cases like this as it cuts out the long tail behavior and makes the whole system more predictable.
cosmotic 5y ago
Using SQL workarounds/hacks just makes the code harder to read and harder for the query optimizer to detect the original intention and optimize in the future.
- talos 5y ago
  This is true, but the workaround proposed in the article is not a change to the query at all. It's adding another index, which is a pretty classic case of "proper" usage of query analysis (as opposed to confusing/hacky mutations of the query itself).
  - shock-value 5y ago
    Unless I'm misreading they didn't add another index. They actually just reduced the original index by removing an indexed expression and excluding rows that didn't match that expression instead.
valyagolev 5y ago
happened to us but for an even weirder case. postgres wasn’t doing a simple index-only scan because, as i learned from googling, the row of the table wasn’t that much wider than the index. creating a huge extra column helped i wish there was no fancy query planner at all, and definitely no obligatory ones. it’s 2021, we either learn how to plan computation, or can afford not to care
- hobs 5y ago
  There's so many cases where this is the wrong opinion that it is mind boggling to see. There's so many queries NOT blogged about that just do the thing you want pretty much all the time, for no extra work on your part.
  - valyagolev 5y ago
    query planner is a very complex, unpredictable piece of software that makes decisions I end up paying for. yes, I want it dismantled, and the same problem addressed somehow otherwise. maybe via dumber "query planners" and better "hints", even though the word "hint" doesn't go far enough in the direction of control that I need. I won't be sorry about this opinion even if you're used to this state of affairs (i edited my comment very slightly to avoid getting more responses like yours)
jeffbee 5y ago
You should be able to dictate the exact query plan to every database. SQL is a bad and obsolete API.
infogulch 5y ago
SQLite has a nice solution to this problem: you can pin queries to use specific plans. By strategically controlling when statistics are redone, you can optimize a database for a specific set of queries. This does have the downside that it doesn't react to changes dynamically, but that is a good tradeoff in many circumstances.
albertopv 5y ago
I'm not a dba, and I admit having years of experience on Sql Server and only few recent months with postgres, and I know it's not a popular opinion, but so far, generally speaking, including admin stuff, I found Sql Server to be superior. It has its problems, of course, but with Sql Server I found better tooling and OOTB experience, easier maintenance, more features easier to use. E.g. do something like sp_help or sp_helptext exists in postgres? Real and simple case insensitive search, even just on a single comparison in a query? Real collations? Page and row data compression (in Sql Server you can obtain good perf improvement enabling compression thanks to fewer IO ops)? Column encryption? Query store, with the possibility to choose a plan? Graphical exec plan? A true sql language with, e.g., variable declaration in a statement (DO postgres is a hack, not a solution) outside of function or stores procedure? Manual Vacuum, really? Never heard of in sql server (shrink is different, vacuum equivalent is an automatic background job). Sql Server is quite predictable, or maybe it's me that I know it much more than postgres. However, there are many postgres feature really missing in Sql Server, or added only in recent versions (consider that many clients are still using Sql Server 2012, a few even 2008R2!), idempotent DDL is much easier on postgres than Sql Server older than v2016, json support is waaaay better, many more useful data type... I know nothing about replicas, clusters, failover etc, those have always been managed by proper DBAs.
- deleted 5y ago
  [deleted]
trhway 5y ago
They added an index to a table getting very active flow of writes. It would be very interesting to see the write performance change too, so the picture would be more balanced.
icsa 5y ago
I had a similar situation a few years back. Postgres has constructs that are optimization barriers. Once I understood this and reviewed the Postgres source code to determine the semantics of the relevant constructs, it was possible to get a 10X improvement for complex set of queries that had already been optimized for several years.
FabHK 5y ago
A long time ago, our team tried to implement basically a versioned time-series key-value store (ie, you have many keys with daily values, and in addition to key and value it also stored an "as-of" date, so that you could override or correct a value for a specific value date by specifying a newer "as-of" date). To query a value for a given date, you'd specify key and value date, and it would then give you the value with the latest "as-of" date. Almost always, there'd only be one "as-of" date, and rarely, when a correction had happened, there'd be two, extremely rarely three. Now, the SQL query then was some sort of group_by and join where as_of = max over the group of as_of. Conceptually, just find the key and date, then pick the last as_of (if there are more than one). But the stupid engine did this massive self-join with a hash-join, and it was terribly slow. The conceptually simple idea turned out to be a disaster. Made me realise a) how little I know about DBs. b) that you can get a correct solution in a language by understanding the language a bit; but to obtain an efficient solution, you need to understand the language rather well. (Note: the SO question below is just about this topic, and I assume (hope) there are performant solutions to this nowadays. (I worked on this back when Microsoft SQL Server had just introduced an XML datatype for structured unstructured columns...) https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column https://stackoverflow.com/questions/121387/fetch-the-row-whi... )
- derekperkins 5y ago
  We tried the same thing a few years ago in MySQL that was randomly non-performant: 4000x slower. Still waiting for that to be fixed.
- barrkel 5y ago
  Window functions can help here. You can try a self-join with MySQL (and it won't do a hash join because it doesn't have it; it didn't have hash joins at all before 8.10 and it still doesn't have hash outer joins, and what you want to eliminate the older rows is an anti-join, i.e. asserting null on a left join) but honestly you're better off sorting by timestamp and using a manual window when processing the results.
- andreareina 5y ago
  Standard sql: select key, value, asof from ( select *, row_number() over (partition by key order by asof desc) as rn from <table> ) t where rn = 1 postgresql: select distinct on (key) key, value, asof from <table> order by key, asof desc The DISTINCT ON clause takes the first row that the query returns, which the ORDER BY makes sure is the latest value Disclaimer: I've used both methods, but haven't tested the perf against each other or the self-join.
BitPirate 5y ago
The other thing i'm currently struggling with is the slow parameter sniffing in SQL server. We would really like to switch to the official mssql-jdbc driver but the performance is abysmal: https://github.com/microsoft/mssql-jdbc/issues/1196#issuecomment-584655612 https://github.com/microsoft/mssql-jdbc/issues/1196#issuecom...

65 points by kmdupree 5y ago

That's an interesting limitation I didn't know. Though I haven't really relied on index-only scans much so far, the limitations regarding the visibility map seemed a bit too hard to judge to me. I assume that this works usually, but it's always a bit intimidating when a feature talks about limitations in DB internal data structures you didn't even know existed. What surprises me here a bit is that this only provides a factor of two improvement. I would have expected the index to be much smaller than the table, though they include a bunch of columns in the index. At that ratio I'd be a bit worried about consuming a serious amount of space for that speedup, but that's impossible to judge without knowing the details. And if the performance is important enough here of course this is still worth it even if the index is large.

kmdupree 5y ago
Thanks for reading! I hear you on visibility maps being intimidating. In practice, I haven't seen any cases where visibility map issues have prevented an index-only scan. But we did initially think that the visibility map was to blame for what we were seeing! RE space of the index: it cost us about 1% of the free disk space on our workers. It was worth it for this particular feature.

mst 5y ago

I confess that 'type' sounds to me like the sort of thing that's going to appear in a WHERE clause often enough that I might as well make an enum for it and materialise that out on insert whether by trigger or otherwise. Neat trick for the meantime though, I'm sure making a schema change like the one I'm thinking of would be a colossal pain in the buttocks if decided late in the run up to shipping a feature.

fabian2k 5y ago
I'm a huge, huge fan of JSONB columns in Postgres, but I'd agree here. Anything that is consistent and important enough should be pulled out of JSON and put into a real column. Postgres has some impressive functionality to query JSON, but it's not the same as with real columns and you run into performance issues much, much quicker.
- asah 5y ago
  2c: in most cases, a Postgres index is (performance wise) identical to a "real column" just without the name, and indeed you have more control over the data structure for an index, and indexes can include lossy compression (e.g. partial indexes) unlike a real column... (obviously, the challenge is getting pg to use your index for a given query... for that, I like to use VIEWs to help query authors write queries that use the indexes)
  - saurik 5y ago
    Yeah. This wasn't the case a while back--PostgreSQL was missing support for what I think people call covered indexes, and so would only use the index to filter a set of candidate rows and then still had to go digging around in the real tuple to verify it matched--but I am pretty sure they changed that a while back.
kmdupree 5y ago
You're exactly right that our schema could be better and that it's non-trivial to execute on a schema change, especially because we actually run a distributed Postgres cluster via Citus AND we use a special sharding method that we manage manually. We actually just started working towards how we might do a schema change for the 1+ million shards in our cluster. Hopefully, we'll be able to write up some learnings on the schema change after its done. :)
- mst 5y ago
  Right, your current solution is what I'd classify as an "expedient field hack" if I were the one implementing it, and I do mean that positively :D I look forwards to comparing notes when that post comes out.

jve 5y ago

I have an experience with MS SQL Server, so can only comment about that. And regarding performance, mainly with old 2008R2 version. The query planner isn't very clever at times. "At times" actually makes it worse, because performance bugs surface sometimes. Anyways, the result is multiple orders of magnitude difference. What takes 0.3-2ms suddenly takes hundreds of milliseconds or even seconds to complete. Multiply that with millions of execution count and there is a problem. Sometimes SQL Server WILL NOT CHOOSE covering index (with many include columns), because it also evaluates index size. And if SQL thinks that some seek on clustering index is specific enough for parameter value A, then it is disaster with parameter B. Good thing SQL server features plan guides, where you can tell server which index to use or provide other hints. Saves the world when dealing with 3rd party applications. The stuff you have to do deal with your db grows as row count grows. If configured well, can support billions of rows, as we see from this post.

shock-value 5y ago
> Good thing SQL server features plan guides If only Postgres maintainers got this message.
- protomyth 5y ago
  I remember working on a Sybase 11.5 database that basically required forcing of indexes for many tables because the query planner would always go off the rails. We had to run a script in production that would print out the query plan of the currently active queries that were taking forever. Without being able to force indexes, I have no idea how we'd of fixed the problem.
  - shock-value 5y ago
    I like Postgres but this aspect is one of its shortcomings. In Postgres you usually have to rework your query in weird ways and hope that it's possible to get the optimizer to make the right choice. Even then, due to its usage of statistics, it could switch plans at any time as your data grows/changes.
- 5y ago

bluedino 5y ago

In articles like these, I wish they would go a little deeper and says why the system does what it does. Perhaps the default behavior is geared towards low-memory servers, or avoids disk writes, or the data is usually expected to be of a certain type. Perhaps the system "isn't very smart" like it says, or it was originally intended for other purposes.

fabian2k 5y ago
I don't know the internals here, but I think I can make a plausible guess here. The index-only scan is generally triggered only when every column in your query is contained in the relevant index. In this case the index is a functional index, so it's not only plain columns in there. The function needs the data column, but the functional index already stores the result of the function. To the query planner it looks like the query needs the data column, while it actually doesn't need it. This is a bit of an uncommon case that would need special handling in the query planner. So it's not that odd that it is not implemented yet, though I would suspect that it'll be added at some point.
talos 5y ago
I definitely agree with you, but in this case the Postgres team itself frames the suboptimal behavior as such: > However, PostgreSQL's planner is currently not very smart about such cases. (From https://www.postgresql.org/docs/10/indexes-index-only-scans.html https://www.postgresql.org/docs/10/indexes-index-only-scans....) I suspect this is just a rough edge. Many types of functions may not be consistent, and therefore would need to be re-executed on the original data. Perhaps some of the planner functionality here predates function notation like `IMMUTABLE`.

orthoxerox 5y ago

And yet Postgres still refuses to add hints. I'd rather deal with a nonexistent query optimizer, like the one in Clickhouse, than with an insufficiently smart one that I can't control.

bmurphy1976 5y ago
I haven't done much database work in recent years, but this was always one of my frustrations. I'd take predictability over max performance any day.
mike_hock 5y ago
A hint won't make the query planner use an index column it doesn't know it can use, though.
- lazide 5y ago
  It does tell the query planner it can use it though.
  - deleted 5y ago
    [deleted]
  - SpicyLemonZest 5y ago
    "Doesn't know it can use" in this context means the system probably doesn't know how to use it, even if a hint suggests it should.
    - lazide 5y ago
      Many times it's not that, or specifically that type of behavior doesn't cause the weird 'something randomly blows up' type problems. It's that it is using index size vs on disk size or something for specific query values and deciding to do something kind of dumb, like a full range scan, because it isn't taking into account the disk data is on... well disk, and the index is already loaded into memory because of other queries. Semi-locking it to known good indexes or whatever, or at least overriding it's attempt to be smart, helps usually in cases like this as it cuts out the long tail behavior and makes the whole system more predictable.

cosmotic 5y ago

Using SQL workarounds/hacks just makes the code harder to read and harder for the query optimizer to detect the original intention and optimize in the future.

talos 5y ago
This is true, but the workaround proposed in the article is not a change to the query at all. It's adding another index, which is a pretty classic case of "proper" usage of query analysis (as opposed to confusing/hacky mutations of the query itself).
- shock-value 5y ago
  Unless I'm misreading they didn't add another index. They actually just reduced the original index by removing an indexed expression and excluding rows that didn't match that expression instead.

valyagolev 5y ago

happened to us but for an even weirder case. postgres wasn’t doing a simple index-only scan because, as i learned from googling, the row of the table wasn’t that much wider than the index. creating a huge extra column helped i wish there was no fancy query planner at all, and definitely no obligatory ones. it’s 2021, we either learn how to plan computation, or can afford not to care

hobs 5y ago
There's so many cases where this is the wrong opinion that it is mind boggling to see. There's so many queries NOT blogged about that just do the thing you want pretty much all the time, for no extra work on your part.
- valyagolev 5y ago
  query planner is a very complex, unpredictable piece of software that makes decisions I end up paying for. yes, I want it dismantled, and the same problem addressed somehow otherwise. maybe via dumber "query planners" and better "hints", even though the word "hint" doesn't go far enough in the direction of control that I need. I won't be sorry about this opinion even if you're used to this state of affairs (i edited my comment very slightly to avoid getting more responses like yours)

jeffbee 5y ago

You should be able to dictate the exact query plan to every database. SQL is a bad and obsolete API.

infogulch 5y ago

SQLite has a nice solution to this problem: you can pin queries to use specific plans. By strategically controlling when statistics are redone, you can optimize a database for a specific set of queries. This does have the downside that it doesn't react to changes dynamically, but that is a good tradeoff in many circumstances.

albertopv 5y ago

I'm not a dba, and I admit having years of experience on Sql Server and only few recent months with postgres, and I know it's not a popular opinion, but so far, generally speaking, including admin stuff, I found Sql Server to be superior. It has its problems, of course, but with Sql Server I found better tooling and OOTB experience, easier maintenance, more features easier to use. E.g. do something like sp_help or sp_helptext exists in postgres? Real and simple case insensitive search, even just on a single comparison in a query? Real collations? Page and row data compression (in Sql Server you can obtain good perf improvement enabling compression thanks to fewer IO ops)? Column encryption? Query store, with the possibility to choose a plan? Graphical exec plan? A true sql language with, e.g., variable declaration in a statement (DO postgres is a hack, not a solution) outside of function or stores procedure? Manual Vacuum, really? Never heard of in sql server (shrink is different, vacuum equivalent is an automatic background job). Sql Server is quite predictable, or maybe it's me that I know it much more than postgres. However, there are many postgres feature really missing in Sql Server, or added only in recent versions (consider that many clients are still using Sql Server 2012, a few even 2008R2!), idempotent DDL is much easier on postgres than Sql Server older than v2016, json support is waaaay better, many more useful data type... I know nothing about replicas, clusters, failover etc, those have always been managed by proper DBAs.

deleted 5y ago
[deleted]

trhway 5y ago

They added an index to a table getting very active flow of writes. It would be very interesting to see the write performance change too, so the picture would be more balanced.

icsa 5y ago

I had a similar situation a few years back. Postgres has constructs that are optimization barriers. Once I understood this and reviewed the Postgres source code to determine the semantics of the relevant constructs, it was possible to get a 10X improvement for complex set of queries that had already been optimized for several years.

FabHK 5y ago

A long time ago, our team tried to implement basically a versioned time-series key-value store (ie, you have many keys with daily values, and in addition to key and value it also stored an "as-of" date, so that you could override or correct a value for a specific value date by specifying a newer "as-of" date). To query a value for a given date, you'd specify key and value date, and it would then give you the value with the latest "as-of" date. Almost always, there'd only be one "as-of" date, and rarely, when a correction had happened, there'd be two, extremely rarely three. Now, the SQL query then was some sort of group_by and join where as_of = max over the group of as_of. Conceptually, just find the key and date, then pick the last as_of (if there are more than one). But the stupid engine did this massive self-join with a hash-join, and it was terribly slow. The conceptually simple idea turned out to be a disaster. Made me realise a) how little I know about DBs. b) that you can get a correct solution in a language by understanding the language a bit; but to obtain an efficient solution, you need to understand the language rather well. (Note: the SO question below is just about this topic, and I assume (hope) there are performant solutions to this nowadays. (I worked on this back when Microsoft SQL Server had just introduced an XML datatype for structured unstructured columns...) https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column https://stackoverflow.com/questions/121387/fetch-the-row-whi... )

derekperkins 5y ago
We tried the same thing a few years ago in MySQL that was randomly non-performant: 4000x slower. Still waiting for that to be fixed.
barrkel 5y ago
Window functions can help here. You can try a self-join with MySQL (and it won't do a hash join because it doesn't have it; it didn't have hash joins at all before 8.10 and it still doesn't have hash outer joins, and what you want to eliminate the older rows is an anti-join, i.e. asserting null on a left join) but honestly you're better off sorting by timestamp and using a manual window when processing the results.
andreareina 5y ago
Standard sql: select key, value, asof from ( select *, row_number() over (partition by key order by asof desc) as rn from <table> ) t where rn = 1 postgresql: select distinct on (key) key, value, asof from <table> order by key, asof desc The DISTINCT ON clause takes the first row that the query returns, which the ORDER BY makes sure is the latest value Disclaimer: I've used both methods, but haven't tested the perf against each other or the self-join.

BitPirate 5y ago

The other thing i'm currently struggling with is the slow parameter sniffing in SQL server. We would really like to switch to the official mssql-jdbc driver but the performance is abysmal: https://github.com/microsoft/mssql-jdbc/issues/1196#issuecomment-584655612 https://github.com/microsoft/mssql-jdbc/issues/1196#issuecom...