Hacker News Reader

Comment by thrownaway2424 | Hacker News Reader

I always wondered why databases don't allow the direct specification of the query plan, instead of forcing SQL developers and DBAs to try to guess the right words that produces the right query plan, usually. It seems like it would be a lot easier to express the query plan in, say, a protobuf, rather than parsing a DSL on every query.

View on HN

I know this is a simplistic and ideological notion, but anybody who can manually plan a query better than the query planner should be using their time to write better query planners, optimizers, and statistics collectors as opposed to manually writing query plans.

Most databases have ways for you to specify the exact plan you want used already. Since you mentioned SQL Server, you can force join order, force index usage, force the type of join used (loop, merge, hash), etc.

There are terrible drawbacks using any of those techniques (data changes, indexes being modified / dropped, etc) which is why, imo, they should very rarely be used. However, 'allowing direct specification of the query plan' is already implemented in that respect.

The better way to address the problem of different parameters causing performance issues is forcing recompiles which will solve the problem 95% of the time.

This is what collecting and managing statistics[0][1] is about.

I know Oracle and Postgres implements this -- don't know about SQLServer, MySQL, etc...

[0] http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats....

[1] http://www.postgresql.org/docs/current/static/runtime-config...

edit: found/added info for postgres

Sure but I don't want to influence the query plan, I want to dictate it.

I know at least Microsoft's SQL Server allows you to simulate this by fixing a query plan: https://technet.microsoft.com/en-us/library/Cc917694.aspx

Having worked with SQL Server for years as a DBA, I've never had to use it once, but I was never working with databases over, say, 1 TB. So while we did encounter slow query problems, they could usually be resolved by introducing reliable indexes, and the performance cost of this was low.

Reply on news.ycombinator.com