4 ms·

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 ver

by 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.
- azth 5y ago
  Looks like we could use a "hint" ;-)
- asah 5y ago
  FYI one method is to use CTEs (WITH) to force Postgres via materialization. https://www.postgresql.org/docs/current/queries-with.html https://www.postgresql.org/docs/current/queries-with.html
protomyth 5y ago
The worst part is database performance goes from great to sucky with no in between. Once a query's plan make it fall off a cliff, you really need the hints (forced indexes) that SQL Server provides to get the plan back to acceptable. Even updating the statistics tends not to fix the problem.

by jve 5y ago

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.
azth 5y ago
Looks like we could use a "hint" ;-)
asah 5y ago
FYI one method is to use CTEs (WITH) to force Postgres via materialization. https://www.postgresql.org/docs/current/queries-with.html https://www.postgresql.org/docs/current/queries-with.html

protomyth 5y ago

The worst part is database performance goes from great to sucky with no in between. Once a query's plan make it fall off a cliff, you really need the hints (forced indexes) that SQL Server provides to get the plan back to acceptable. Even updating the statistics tends not to fix the problem.