3 ms·

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?

mickeyp 3y ago
You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
- _a_a_a_ 3y ago
  Poster said oracle not PG
  - mardifoufs 3y ago
    Total db noob here: is Oracle's query planner better? What's the difference between the two?
    - _a_a_a_ 3y ago
      This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
  - mickeyp 3y ago
    Oracle has query hints; pg does not. Query planner hinters are useful. That's my point.
zaptheimpaler 3y ago
No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.
- _a_a_a_ 3y ago
  You're right, I meant a failure of the DB/optimiser not the person. Thanks. No they're not perfect but I need them rarely with MSSQL > An optimizer will quickly run into NP-hard problems like join ordering As a start, dynamic planning based on cardinality estimation based on stats. And then some. It explodes horribly, yes.

mickeyp 3y ago

You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.

_a_a_a_ 3y ago
Poster said oracle not PG
- mardifoufs 3y ago
  Total db noob here: is Oracle's query planner better? What's the difference between the two?
  - _a_a_a_ 3y ago
    This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
- mickeyp 3y ago
  Oracle has query hints; pg does not. Query planner hinters are useful. That's my point.

zaptheimpaler 3y ago

No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.

_a_a_a_ 3y ago
You're right, I meant a failure of the DB/optimiser not the person. Thanks. No they're not perfect but I need them rarely with MSSQL > An optimizer will quickly run into NP-hard problems like join ordering As a start, dynamic planning based on cardinality estimation based on stats. And then some. It explodes horribly, yes.