4 ms·

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat

by Pxtl 7y ago

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat files.

carlosf 7y ago
You might even call it a Data Lake and get away with it.
da_chicken 7y ago
I've found that SQL Server's is generally pretty good, with the big pitfall being the system's timeout for the query planner/compiler. Query compilation timeouts can be really frustrating to work on because, often, the query's complexity is a requirement. The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always. The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.
sjwright 7y ago
I found SQL Server's query optimiser to be magical, but it relies on its table statistics being somewhat correct. Every now and again they liked to suddenly become wrong enough that queries go from magical to catastrophic mush. (Most recent version I've used was SQL Server 2008.)
- ants_a 7y ago
  All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model and production date. If you are looking for the latest Ford F-150 then the best plan is to just start looking backwards by date and you will find one soon enough. Much faster than looking up all F-150s and picking the latest one. On the other hand, if you are looking for latest Ford Model-T, that plan is going to be catastrophically terrible, going through 93 years of car production before finding the correct one.
  - m_mueller 7y ago
    Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale horizontally. Granted, it's hard to design the data structures in the design space of normalization (many joins) vs. read performance (few joins) - but at least it's all laid bare to reason about.
    - sjwright 7y ago
      Sorry, but hard disagree. For all the failings of SQL systems, they work staggeringly well 99% of the time as long as the operator understands how they work. No-SQL systems do have their place, but all too often their choice stems from a failure to understand SQL-based databases. (And the hard truth: unless you’re doing something especially novel there’s a lot less difference between map/reduce and a bog standard table index than no-SQL proponents would have you believe.) In my estimation, if you're not already intimately familiar with the intricacies of performant SQL, chances are you're not playing in a space where a no-SQL architecture is an appropriate fit anyway. And you're certainly not in a position to make an informed decision between SQL and no-SQL. It's a much better use of this hypothetical person's time to set up Mariadb or Sqlite and do a deep dive into the fundamentals query performance.
      - m_mueller 7y ago
        Just to clarify: I'm not saying there is a significant difference between map views and indices, I'm saying I prefer the directness of programming with them over trusting the query planner and table statistics. In couch, to do a table scan, I need to program it as such, otherwise I'm forced to use a view - things like forgotten indices for some edge cases are impossible. Sure takes longer to do things, but when performance issues arise it's easy to reason about while with SQL based DBs I've seen a lot of time being spent at that stage. They work well 99%, and then that remaining 1% takes 99% of your time.

by Pxtl 7y ago

I've generally found SqlServer's query optimizer to be a hellish nightmare of broken dreams, so if Postgres' is even worse I'm giving up and going back to flat files.

carlosf 7y ago

You might even call it a Data Lake and get away with it.

da_chicken 7y ago

I've found that SQL Server's is generally pretty good, with the big pitfall being the system's timeout for the query planner/compiler. Query compilation timeouts can be really frustrating to work on because, often, the query's complexity is a requirement. The only other problem is the parameter sniffing problem for stored procedures, although OPTIMIZE FOR UNKNOWN or specified values seem to work fairly well in my experience, though obviously not always. The real failing is that a common solution to a view query hitting the compiler timeout is to replace it with a stored procedure of some kind. However, if you're not careful you'll run into the parameter sniffing problem with stored procedures! So you run into one caveat and your attempted solution runs into the other one.

sjwright 7y ago

I found SQL Server's query optimiser to be magical, but it relies on its table statistics being somewhat correct. Every now and again they liked to suddenly become wrong enough that queries go from magical to catastrophic mush. (Most recent version I've used was SQL Server 2008.)

ants_a 7y ago
All cost based optimisers rely on statistics being correct. And inevitably there will be a case where they aren't. The problem with PostgreSQLs optimiser, and I assume with others too, is that it's too risk happy. It's optimizing for the average case based on the statistics, when people actually tend to care about the worst case. As an example, say you have a database of all cars ever produced with indexes on model and production date. If you are looking for the latest Ford F-150 then the best plan is to just start looking backwards by date and you will find one soon enough. Much faster than looking up all F-150s and picking the latest one. On the other hand, if you are looking for latest Ford Model-T, that plan is going to be catastrophically terrible, going through 93 years of car production before finding the correct one.
- m_mueller 7y ago
  Your example illustrates perfectly how a descriptive query language simply can't work in the general case for something other than ad-hoc workflows where performance isn't a big concern. Why are we all doing this instead of engineering DB access procedures directly? This is one thing I really liked about CouchDB and its map/reduce based access - stop trying to be smart with queries, instead be dead simple to scale horizontally. Granted, it's hard to design the data structures in the design space of normalization (many joins) vs. read performance (few joins) - but at least it's all laid bare to reason about.
  - sjwright 7y ago
    Sorry, but hard disagree. For all the failings of SQL systems, they work staggeringly well 99% of the time as long as the operator understands how they work. No-SQL systems do have their place, but all too often their choice stems from a failure to understand SQL-based databases. (And the hard truth: unless you’re doing something especially novel there’s a lot less difference between map/reduce and a bog standard table index than no-SQL proponents would have you believe.) In my estimation, if you're not already intimately familiar with the intricacies of performant SQL, chances are you're not playing in a space where a no-SQL architecture is an appropriate fit anyway. And you're certainly not in a position to make an informed decision between SQL and no-SQL. It's a much better use of this hypothetical person's time to set up Mariadb or Sqlite and do a deep dive into the fundamentals query performance.
    - m_mueller 7y ago
      Just to clarify: I'm not saying there is a significant difference between map views and indices, I'm saying I prefer the directness of programming with them over trusting the query planner and table statistics. In couch, to do a table scan, I need to program it as such, otherwise I'm forced to use a view - things like forgotten indices for some edge cases are impossible. Sure takes longer to do things, but when performance issues arise it's easy to reason about while with SQL based DBs I've seen a lot of time being spent at that stage. They work well 99%, and then that remaining 1% takes 99% of your time.