The query planner is top notch. I rarely have to worry about query plans. MSSQL will take my 'readable' code and make an efficient plan out of it. Whereas in mysql and postgres there are many times when I have to write less readable SQL code because their query planners are not able to optimize as well. Ex: mysql sometimes has issues with nested statements, and postgres has fences on CTEs.
I read this while I'm right in the middle of trying to figure out why a linked server query which is a simple join on the same table, on two indexed columns, is taking 5 seconds.
I'm not saying MSSql isn't really nice. I'm just saying I found the timing of reading your comment amusing.
> To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance
I'm honestly not sure how much of that would be related to the query planner vs linked server network code.
Speaking of which, linked servers (cross OS, cross SQL type database queries) are native in MSSQL, but require a third party odbclink in postgres, which does not appear to have been updated since 2010[0].
Linked servers in Postgres can be implemented using foreign data wrappers (part of the SQL/MED implementation in Postgres - MED = management of external data which is actually in the SQL spec):
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
but stuff like "Does not compile with PostgreSQL >= 9.2!", "A patched, but completely untested version", and "The current version does not yet support WHERE clause push-down, column push-down, JOIN push-down, or write operations."[0] are riskier (and a harder sell to management) than having the functionality backed in the core engine and admin interfaces.
Up to a point.
Unfortunately this fairly rock solid planner means that a lot of people will keep throwing things at it until until explosions.
SQL Server's parameter sniffing often makes me sad, and having to turn on trace flags effectively by default is still fairly annoying.
