4 ms·

Query hints are one reason. I'm not a fan of Oracle as a company, but for certain usage patterns you need query hints and Postgres doesn't support them.

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 ago
  I'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 ago
  You 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.
devit 6y ago
I don't think it's likely that you ever really need query hints. Even if updating analyses, creating indices, changing the query, etc. fails, you can always either use CTEs or temporary tables, or use a stored procedure that manually iterates over results to implement whatever strategy is desired. It might be more time consuming than having query hints, although this is compensated by the fact that almost always queries just work after creating appropriate indices.
- jdm2212 6y ago
  > It might be more time consuming than having query hints The queries in question can't be allowed to get any slower than they already are. They bottleneck certain critical uses.
  - paulryanrogers 6y ago
    Do query hints lock in the implementation though? I imagine that DB upgrades could have different performance characteristics both with and without hints.
  - necovek 6y ago
    I believe the GP post was referring to restructuring your query so you make Postgres hit better indices (eg. when you know the distribution of data but can't easily apply a partial index). A common way to do that in Postgres is to use subselects for a criteria, and the join in the outer query. Coming up with such queries is "more time consuming". And yes, this approach is fragile even in Postgres (version or data changes might affect the performance, or you might be stuck with a worse query when query planner becomes smarter), so I imagine query hints in Oracle have the same problem.
  - devit 6y ago
    I meant time consuming in developer time, sorry.
gurjeet 6y ago
EnterpriseDB's Advanced Server (based on Postgres) has the query optimizer hints feature [1]. Disclosure: I implemented the Optimizer Query Hints feature at EnterpriseDB. [1]: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/11/Database_Compatibility_for_Oracle_Developers_Guide.1.038.html https://www.enterprisedb.com/edb-docs/d/edb-postgres-advance...

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 ago
I'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 ago
You 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.

devit 6y ago

I don't think it's likely that you ever really need query hints. Even if updating analyses, creating indices, changing the query, etc. fails, you can always either use CTEs or temporary tables, or use a stored procedure that manually iterates over results to implement whatever strategy is desired. It might be more time consuming than having query hints, although this is compensated by the fact that almost always queries just work after creating appropriate indices.

jdm2212 6y ago
> It might be more time consuming than having query hints The queries in question can't be allowed to get any slower than they already are. They bottleneck certain critical uses.
- paulryanrogers 6y ago
  Do query hints lock in the implementation though? I imagine that DB upgrades could have different performance characteristics both with and without hints.
- necovek 6y ago
  I believe the GP post was referring to restructuring your query so you make Postgres hit better indices (eg. when you know the distribution of data but can't easily apply a partial index). A common way to do that in Postgres is to use subselects for a criteria, and the join in the outer query. Coming up with such queries is "more time consuming". And yes, this approach is fragile even in Postgres (version or data changes might affect the performance, or you might be stuck with a worse query when query planner becomes smarter), so I imagine query hints in Oracle have the same problem.
- devit 6y ago
  I meant time consuming in developer time, sorry.

gurjeet 6y ago

EnterpriseDB's Advanced Server (based on Postgres) has the query optimizer hints feature [1]. Disclosure: I implemented the Optimizer Query Hints feature at EnterpriseDB. [1]: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/11/Database_Compatibility_for_Oracle_Developers_Guide.1.038.html https://www.enterprisedb.com/edb-docs/d/edb-postgres-advance...