3 ms·
PostgreSQL provides a variety of options for tuning both the query planner's strategy and the per-table statistical analyses that are used as input to the query
by zozbot234 6y ago
PostgreSQL provides a variety of options for tuning both the query planner's strategy and the per-table statistical analyses that are used as input to the query planner itself. These tuning methods are actually far more robust than the query hints that are commonly used in other databases, including Oracle.
- jdm2212 6y agoI'm not a DB expert by any means, but the DB experts at my job are pretty insistent that (given the way we deploy our software) it's not practical to do in Postgres what we can do with Oracle query hints.
- aeyes 6y agoYou usually have one query that you need to optimize but hundreds more that query the same data, therefore messing with the statistics of a table is hardly ever optimal. Usually it's some three-way join with n:n:n relations which breaks Postgres' neck. I know exactly where the query should start from, statistics can't possibly know because they have no knowledge about the data distribution in adjacent tables. I use CTE to "fix" this but the query would be faster without it. Another issue is that plans can just fall over and you have absolutely no tools available to see what the old plan was, unless you ran explain before and saved the plan. You also have no way to force the old plan while you analyze the issue. This has caused more production outages than I'm willing to admit. With Oracle it would take me one minute to see what the issue is, and I could make the database use the old plan without changing a single line of code.