4 ms·

Yes having worked with MS SQL Server for many years then taking a look at PG the first thing that really surprised me was lack of hints. Having been through sim

Yes having worked with MS SQL Server for many years then taking a look at PG the first thing that really surprised me was lack of hints. Having been through similar situations with MSSQL but being able to force it to do the right thing was a life saver. This is about repeatability and stability. I would rather the DB run slower so long as it does so consistently and repeatably until a human can profile the issue and make a source code change to speed it up. Having the optimizer just decide in the middle of the night on one of a number of different production databases is a nightmare. MSSQL now days lets you save and load full plans and freeze plans, but the reality is just some simple hints go a long way, like which index to use and what type of join to use. Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in. The client can manually prepare a plan but only reuse on the same connection.

throwdbaaway 6y ago
Lack of hint is definitely a controversial topic. There are situations where there is only one obvious way to execute the query, in which case a query hint would help to prevent the database engine from going berserk. However, there are also situations where different query plans should be used according to the parameters, in which case a query hint would harm. We now also have some empirical evidence on how the query planner evolve/devolve based on this decision. InnoDB always relies heavily on query hint, and recently it has given up on trying to get the planner to support some simple pagination queries correctly, after killing too many production systems that didn't add a hint: https://dev.mysql.com/worklog/task/?id=13929 https://dev.mysql.com/worklog/task/?id=13929. It basically declares that "we know our query planner sucks for this class of queries, here's a flag to disable that portion of the query planner" Meanwhile, in recent releases, Postgres started to allow statistics to be created for correlated columns, so that the query planner can have the necessary information to come up with the correct plan: https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correlated-column-crusade/ https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correla... > Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in. This is actually the thing I hate the most about MSSQL. If I allow plans to be cached, then some queries would end up using some bad plans intended for parameters with very different data distribution. If I disallow plans to be cached, then the query planner can take 1~2 second just to come up with a plan for a rather simple query with 2 levels of nesting. A rock and a hard place.

throwdbaaway 6y ago

Lack of hint is definitely a controversial topic. There are situations where there is only one obvious way to execute the query, in which case a query hint would help to prevent the database engine from going berserk. However, there are also situations where different query plans should be used according to the parameters, in which case a query hint would harm. We now also have some empirical evidence on how the query planner evolve/devolve based on this decision. InnoDB always relies heavily on query hint, and recently it has given up on trying to get the planner to support some simple pagination queries correctly, after killing too many production systems that didn't add a hint: https://dev.mysql.com/worklog/task/?id=13929 https://dev.mysql.com/worklog/task/?id=13929. It basically declares that "we know our query planner sucks for this class of queries, here's a flag to disable that portion of the query planner" Meanwhile, in recent releases, Postgres started to allow statistics to be created for correlated columns, so that the query planner can have the necessary information to come up with the correct plan: https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correlated-column-crusade/ https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correla... > Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in. This is actually the thing I hate the most about MSSQL. If I allow plans to be cached, then some queries would end up using some bad plans intended for parameters with very different data distribution. If I disallow plans to be cached, then the query planner can take 1~2 second just to come up with a plan for a rather simple query with 2 levels of nesting. A rock and a hard place.