4 ms·

I was specifically referring to the optimization fence on CTEs. PG still doesn't have query hints, and it seems like the core team has an ideological opposition

I was specifically referring to the optimization fence on CTEs. PG still doesn't have query hints, and it seems like the core team has an ideological opposition to building query hint capabilities. Their stance has been that query hints often lead to suboptimal behavior because queries often don't change over time, but data sets do, so relying on statistics allows your query plans to change with different data. A query hint, on the other hand, may lead to an optimal query execution today, but a terrible one tomorrow. Over the years Postgres has steadily chipped away at almost all of the reasons why I would ever want query hints. Only two frustrations remain: * the optimizer is not aware of TOAST storage. This means that for tables with lots of large objects (geospatial in my case), the optimizer only sees the costs of the table with a pointer to the large object, as opposed to the cost of pulling table with the large object. * The optimizer only has singular statically configured costs for functions and aggregates, but costs can actually dramatically vary based on the size of the data inputs, the size of the aggregate "window", etc. Overall, these are rarely a big enough deal for me to not choose Postgres. I see the default planner behavior as better than the default planners for both Oracle and SQL Server, but it is still possible that the lack of query hints could be a deal breaker. If you get a chance, give it another try...I think you might be surprised at how far Postgres has come since the 9.X days. As far as number 3, I guess I should clarify that they've dramatically reduced the connection overhead, but I shouldn't have said "fixed". It may or may not be reduced enough for your requirements. Here is a blog post with some links to various commits: https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-improvements-for-handling-large-number-of-connections/ https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-... Some rudimentary benchmark comparisons here: https://github.com/digoal/blog/blob/master/202008/20200817_01.md https://github.com/digoal/blog/blob/master/202008/20200817_0... Also, wrt #4, I found one of the projects to reduce bloat with a different storage engine: https://cybertec-postgresql.github.io/zheap/ https://cybertec-postgresql.github.io/zheap/

cameronh90 5y ago
I do actually use Postgres still, despite its limitations. I dislike their ideological opposition to query hints, partially because I've been bitten enough by the optimiser deciding in production to change the query plan to something that kills performance. If anything, I'd rather be able to pin the entire query plan! Obviously a better optimiser is ideal, but query plan changes are a big risk to app stability that are hard to mitigate. AWS Aurora for PGSQL actually has a query plan management system which helps with this. Anyway, the difference between PG and the others doesn't make up for the licensing costs. Even in the specific cases where MSSQL does far, far better, the licensing costs still eliminate most of the benefit.
- AlisdairO 5y ago
  The query hint thing is a serious issue. I get the concern that people won't report issues and so on, but on heavily loaded DBs having no decent operational response to a bad plan change is really scary. In an ideal world something like oracle's plan stability would be pretty helpful.

cameronh90 5y ago

I do actually use Postgres still, despite its limitations. I dislike their ideological opposition to query hints, partially because I've been bitten enough by the optimiser deciding in production to change the query plan to something that kills performance. If anything, I'd rather be able to pin the entire query plan! Obviously a better optimiser is ideal, but query plan changes are a big risk to app stability that are hard to mitigate. AWS Aurora for PGSQL actually has a query plan management system which helps with this. Anyway, the difference between PG and the others doesn't make up for the licensing costs. Even in the specific cases where MSSQL does far, far better, the licensing costs still eliminate most of the benefit.

AlisdairO 5y ago
The query hint thing is a serious issue. I get the concern that people won't report issues and so on, but on heavily loaded DBs having no decent operational response to a bad plan change is really scary. In an ideal world something like oracle's plan stability would be pretty helpful.