Re:Working around a case where the Postgres planner is “not ...
by shock-value on Monday August 02, 2021 @04:06PM
(#28041928)
> Good thing SQL server features plan guides
If only Postgres maintainers got this message.
Re:Working around a case where the Postgres planner is “not ...
by protomyth on Monday August 02, 2021 @04:09PM
(#28041958)
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.
Re:Working around a case where the Postgres planner is “not ...
by shock-value on Monday August 02, 2021 @04:12PM
(#28041991)
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.
Re:Working around a case where the Postgres planner is “not ...
by asah on Tuesday August 03, 2021 @09:09AM
(#28048682)
FYI one method is to use CTEs (WITH) to force Postgres via materialization.
Re:Working around a case where the Postgres planner is “not ...
by azth on Monday August 02, 2021 @04:30PM
(#28042221)
Looks like we could use a "hint" ;-)
Re:Working around a case where the Postgres planner is “not ...
by protomyth on Monday August 02, 2021 @04:07PM
(#28041938)
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.
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.