[Brakenshire]

How do you build your intuition about creating queries in a way to avoid this?

Is it a matter of having a conceptual model of relational algebra and the way the different db engines work, or is it more an accumulation of heuristics over time for what probably will cause problems, and an iterative process of using EXPLAIN, adjusting the query and seeing what happens?

[tluyben2]

I am old :) But experience is a big thing; I can sniff by just skimming the table definitions where probably something is very wrong. In uni in the 90s I studied both relational theory and formal methods and I had to spend a lot of time figuring out and fixing complexity; if you take a university level book on big O complexity and work through it, you will have a good feeling what software can and cannot do and in what way. That has not really changed; we have more efficient and more cache, we have improved algorithms, but things that cannot be looked up in O(1) are still dangerous and possibly can incur enormous IO even with only a few million records. Naive developers see that things are blazingly fast locally on their laptops and that’s it. I have met, especially in the last few years (In my bubble this is getting worse, quite fast), quite a lot of lead devs that actually do not know what an index is for and so I see entire dbs without any or only on the id field. I know people (for some reasons) do not like ORMs that create tables and indexes, but it would prevent many rookie mistakes if they did.

[jiggawatts]

I can’t remember the last time I came across a non-CotS database schema that has secondary indexes in a significant number. Like more than half a dozen for a hundred tables or more.

I’ve never seen a database use “advanced” features like clustered columnstore or even just page compression.

I just have an email in my inbox from this morning from a small vendor that “doesn’t recommend” columnstore for a database containing 10 TB of numeric metrics in one table.

That would compress to a few gigabytes and query times would go from minutes to milliseconds.

But they “don’t support it”.

Which I now translate to: “we haven’t even flipped through the manual and when we googled it in a panic we didn’t understand it.”

This is how your data is being managed at huge enterprises and government agencies around the world.

[tluyben2]

Exactly. And what I find far far worse is that this is bleeding into startups. I did not express myself too clear above; I don't consult for one client (I used gig, not gigs which I cannot edit anymore); it's different clients every time; I make things fast(er) and then go to the next one. I do this really fast (aka cheap) because I don't look or care about the code (if I would care, this would be a fulltime job). I find stuff by going over the code with grep and checking the storages and then making taking a sledgehammer to beat things into performance.

I had a startup with around $1m seed invest who asked for help (actually one of the board members who is a friend) because they were burning through the 1m too fast and very big cost was the AWS. I wasn't allowed to make changes, but recommended actually adding indexes to the database and adding cache in some places in the code. I also found some strange O(2^n) 'algorithms' in the code but they weren't used much; I recommended not being clever and using libraries or the database (they all had to do with geo pathfinding stuff; do people know how to use google?). I estimate that their costs on AWS would dramatically drop doing that. Instead of doing this, their investors are upping their investment so the company can keep iterating fast.

I kind of understand this to some extent, however these things won't cost too much time and when you are building things the first time they don't cost extra time at all, you just need to know them (yes, i'm trying to be polite and nice about people who create software and do not know about db indexing). Some of these companies will grow to be the next something-you-use-every-day and this is how the data is handled.

Maybe I should write a book about anonymised client misery stories. I have too many and one day I will die and some people will never encounter this; because I usually work in gigs I got via c-level execs, I see many layers of absolute garbage at the same time inside a company, especially inside big ones. People here and on reddit who have never seen these things and think large enterprises are these smooth ran places really should be exposed to the absolute chaos that goes on there.

[jiggawatts]

The knee-jerk reaction I get to any proposed database tuning is: "That sounds expensive, let's just throw more cores at it to solve the scalability issues."

Of course, tuning is often a one-time activity and cores cost money monthly, but they ignore that.

They also ignore that if one user gets a poor experience, then it is by definition not caused by a lack of scale. Conversely, it will cause scalability issues, but that's a side effect and not the root cause.

I'm starting to suspect that 90-98% of all "web-scale" architectures are compensating for errors like this. Nobody has tried to use the "release" build, add an index, or just use a binary data format.

[louissan]

you sir must become a regular dailywtf poster. please! :-)

[pfarrell]

First realize that SQL is not a procedural language, you are only describing the result set. The data store will then create an execution plan which is the actual code that gets run. Learn to read the explain in your data store of choice (very few swe do this). If you have access to a database administrator in your company, befriend and learn from them. Read about how databases store and retrieve data: from sql to data pages. Measure measure measure. Learn about different types of indexes and their trade offs. Remember that “it depends” is the answer to almost every db question and that you should be thinking through all you codes interactions. That is the path to mastery of dbs.

[zasdffaa]

IME just run the DB, pick up and look at the query plans for the most common/time consuming, then add indexes. That's 80% of it. So...

> Is it a matter of having a conceptual model of relational algebra and the way the different db engines work

...no....

> or is it more an accumulation of heuristics over time for what probably will cause problems

...no....

> an iterative process of using EXPLAIN, adjusting the query and seeing what happens?

...that's more like it!

Once you understand the underlying data structures, all the magic goes away. As it should.

[tluyben2]

Yeah, that works. My process is very different, but your advice is better as it hangs more on actionable tactics than learning intuition.

> Once you understand the underlying data structures, all the magic goes away. As it should.

Once you actually understand them, I feel you don’t need explain in most cases; you will simply ‘see’ why certain queries or definitions or structures are bad.

[zasdffaa]

> Once you actually understand them, I feel you don’t need explain in most cases; you will simply ‘see’ why certain queries or definitions or structures are bad.

Ummmm.... my experience pushes me strongly in the other direction. The same query's behaviour can be utterly different depending on the distribution of the data it runs on, and the parameters, and the currency of the statistics, and maybe memory and cpu pressure, and more. One of the heads of the team that wrote SQL server said "don't try to outguess the optimiser". That's very good advice that's served me well, otherwise it's exactly like optimising a program without profiling it; just don't (learnt that hte hard way).

All the internal details of how the server works that you learn are 95% useless until you need them, then they're essential, but just profiling is the best bang for the buck by a long way as a first step, and is always the first step (for me anyway).

[jve]

> The same query's behaviour can be utterly different depending on the distribution of the data it runs on, and the parameters, and the currency of the statistics, and maybe memory and cpu pressure, and more

Yeah, but it is tough and time consuming to test all those parts. Ok, you can specifically try to benchmark by inserting skewed data, trying one and other parameter, but the extra time it takes from you... could be done for critical workloads probably. Maybe a better way would be just to force some particular index you see is suitable for that query, maybe use other hints that will hopefully prevent query optimizer biting down the road.

Luckily SQL Server features Intelligent Query Processing https://docs.microsoft.com/en-us/sql/relational-databases/pe... and Automatic Tuning https://docs.microsoft.com/en-us/sql/relational-databases/au... that tries to solve these pain points. Haven't had experience on how it performs in the wild, as I have to support older SQL Server installs.

[boloust]

> > The same query's behaviour can be utterly different depending on the distribution of the data it runs on, and the parameters, and the currency of the statistics, and maybe memory and cpu pressure, and more

> Yeah, but it is tough and time consuming to test all those parts.

GP was saying that because it is so tough and time consuming to test this, the best thing to do is just to look at the slowest queries and optimize them, instead of trying to identify problems by reasoning from first principles.

[zasdffaa]

Thanks, that's indeed my point.

To clarify an earlier one where I said "The same query's behaviour can be utterly different depending on..." what I was saying was that the same query might be executed completely, totally differently depending on what the optimiser deems best. It might use a merge join with an index, or it might (depending on the data distribution) use a loop join and ignore the index(es) entirely. Same query, different execution.

This is why you should (almost) never use hints unless you know a lot more about the data than the DB server, which is rare (or unless the optimiser is being consistently stupid and very sub-optimal, that happens). If you add an index hint the DB will be forced to use it even if it reduces performance, and it can reduce performance a lot. If you force join orders explicitly you can catastrophically fuck performance - I think I've only ever done that once in production.

So profile, add indexes accordingly, but typically don't force them to be used; let the DB use them, or not, as it feels. Save hints for specially problematic situations.

[drunkpotato]

I mostly work the way you do, but still find explain helpful when my intuition fails. Most notably this usually happens when an index exists but the query manages to miss the index. Most often I experience this when it comes to dates and ORM-built queries that type cast.