4 ms·

I really appreciate that in SQL Server we can just use query hints. I don't think I will ever see eye-to-eye with the PostgreSQL team on this. No query planner

I really appreciate that in SQL Server we can just use query hints. I don't think I will ever see eye-to-eye with the PostgreSQL team on this. No query planner is perfect. SQL Server's planner certainly isn't. We have had the same sort of "query plan suddenly changes in production and everything breaks" incidents, but they are easily fixed with query hints and/or forced plans, and you can be sure that future statistics updates won't break them again.

SigmundA 5y ago
I still have nightmares of SQL servers optimizer taking down production systems at random times due to picking a bad plan, judicious use of hints has made my life much easier and allowed me as a developer to be in control and not have to ask the DB nicely and pray it listens.
- fennecfoxen 5y ago
  I worked for a payments firm that submitted transfers in bulk every day, but had a monthly surge day, and a late cut-off period to submit or cancel payments. It doesn't matter if you can do everything else in 15 minutes, if the query planner decides to take a 30-second query and turn it into a 4-hour nightmare because different customers are making different numbers of payments today, and it trips over some Postgres heuristic. The Postgres query planner was, quite frankly, the enemy. By the time I left we were at the point that we considered it a business risk and were looking at alternatives. If you need queries to run in a predictable amount of time — forget fast, simply predictable — then Postgres is quite simply not fit for purpose.
  - slt2021 5y ago
    these query issues are most of the times not SQL Server problem, they are rather symptom of poor data model architecture, table design. A lot of times I see in operational DB hot data gets mixed with warm and cold data, and your hot path query with ton of JOINs/subqueries will get rekt. Proper redesign and rearchitecture helps to provide large buffer against these problems. Also not everything should be inside SQL server, if you run large query every 5 seconds or something - probably consider using in-memory cache or denormalized model
    - fennecfoxen 5y ago
      Excuse me. You are replying with advice that is fundamentally inappropriate for a batch workload (describing a "hot path query" and suggesting in-memory caches), and it shows that you have not read and comprehended the post I wrote. Besides which, if you need to re-architect and denormalize by moving records in and out of different tables just so you can run what is essentially a monthly report in a predictable (not fast! just predictable) amount of time -- well then, why bother using Postgres in the first place, you might as well bite the bullet and go with something NoSQL at that point, because at least when you read from a given Cassandra partition you know all the results will be right next to each other, 100%; why leave it to chance? you've been burned by Postgres before
      - slt2021 5y ago
        As a developer I never had to blame SQL server, it was all the time develop mistake: poor architecture, poor query, some data quality issues. Dont blame the platform for developers mistakes, thats what I was trying to say
        fennecfoxen 5y ago
        The wise man builds his house upon the rock. The foolish man builds his house upon the sand. I will 100% blame the platform when what it provides is deficient, for selecting a deficient platform is one of the gravest mistakes a developer can make. And Postgres, again, is materially deficient in its query performance predictability, and its developers are insistent that they have no desire to allow the approaches that would mitigate it. If this matters to your application, then the prudent developer will drop Postgres and use a real database.
        SigmundA 5y ago
        Must not have been working with it very long, off the top of my head having to disable the "new" optimizer in Sql 2014 and go back to the 2012 version due to bad plans https://sqlservergeeks.com/sql-server-2014-trace-flags-9481/ https://sqlservergeeks.com/sql-server-2014-trace-flags-9481/ I have been working with it since way back in 6.5 and it has made many obviously wrong decisions in the optimizer over the years, its has gotten better but is by no means perfect, good thing is it has hints unlike PG!
- newlisp 5y ago
  I guess that is why cloud databases like spanner charge big bucks.
rattray 5y ago
You can use pg_hint_plan for hinting query plans to postgres: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan

SigmundA 5y ago

I still have nightmares of SQL servers optimizer taking down production systems at random times due to picking a bad plan, judicious use of hints has made my life much easier and allowed me as a developer to be in control and not have to ask the DB nicely and pray it listens.

fennecfoxen 5y ago
I worked for a payments firm that submitted transfers in bulk every day, but had a monthly surge day, and a late cut-off period to submit or cancel payments. It doesn't matter if you can do everything else in 15 minutes, if the query planner decides to take a 30-second query and turn it into a 4-hour nightmare because different customers are making different numbers of payments today, and it trips over some Postgres heuristic. The Postgres query planner was, quite frankly, the enemy. By the time I left we were at the point that we considered it a business risk and were looking at alternatives. If you need queries to run in a predictable amount of time — forget fast, simply predictable — then Postgres is quite simply not fit for purpose.
- slt2021 5y ago
  these query issues are most of the times not SQL Server problem, they are rather symptom of poor data model architecture, table design. A lot of times I see in operational DB hot data gets mixed with warm and cold data, and your hot path query with ton of JOINs/subqueries will get rekt. Proper redesign and rearchitecture helps to provide large buffer against these problems. Also not everything should be inside SQL server, if you run large query every 5 seconds or something - probably consider using in-memory cache or denormalized model
  - fennecfoxen 5y ago
    Excuse me. You are replying with advice that is fundamentally inappropriate for a batch workload (describing a "hot path query" and suggesting in-memory caches), and it shows that you have not read and comprehended the post I wrote. Besides which, if you need to re-architect and denormalize by moving records in and out of different tables just so you can run what is essentially a monthly report in a predictable (not fast! just predictable) amount of time -- well then, why bother using Postgres in the first place, you might as well bite the bullet and go with something NoSQL at that point, because at least when you read from a given Cassandra partition you know all the results will be right next to each other, 100%; why leave it to chance? you've been burned by Postgres before
    - slt2021 5y ago
      As a developer I never had to blame SQL server, it was all the time develop mistake: poor architecture, poor query, some data quality issues. Dont blame the platform for developers mistakes, thats what I was trying to say
      - fennecfoxen 5y ago
        The wise man builds his house upon the rock. The foolish man builds his house upon the sand. I will 100% blame the platform when what it provides is deficient, for selecting a deficient platform is one of the gravest mistakes a developer can make. And Postgres, again, is materially deficient in its query performance predictability, and its developers are insistent that they have no desire to allow the approaches that would mitigate it. If this matters to your application, then the prudent developer will drop Postgres and use a real database.
      - SigmundA 5y ago
        Must not have been working with it very long, off the top of my head having to disable the "new" optimizer in Sql 2014 and go back to the 2012 version due to bad plans https://sqlservergeeks.com/sql-server-2014-trace-flags-9481/ https://sqlservergeeks.com/sql-server-2014-trace-flags-9481/ I have been working with it since way back in 6.5 and it has made many obviously wrong decisions in the optimizer over the years, its has gotten better but is by no means perfect, good thing is it has hints unlike PG!
newlisp 5y ago
I guess that is why cloud databases like spanner charge big bucks.

rattray 5y ago

You can use pg_hint_plan for hinting query plans to postgres: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan