3 ms·

> Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful Unless you have a small number of static queries,

> Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful Unless you have a small number of static queries, this task isn’t really possible without an observability-solution (reporting the raw query text, the actual execution plan, and runtime and IO stats, et cetera) - otherwise it’s guesswork. …and worse-still if your application has runtime-defined queries, such as having a custom filter builder in your UI. Actually I’ll admit I have no idea how platforms like Jira are able to do that with decent performance (can anyone let me know?) I heap praise on MSSQL’s Query Store feature - which is still a relatively recent addition. I have no idea how anyone could manage query performance without needing 10x the time and effort needed to attach a profiler to a prod DB. …and if these are “edge” databases running on a 100k IoT devices then have fun, I guess.

crazygringo 1y ago
It's not guesswork at all. It basically just requires knowing what it is allowed to look up rows by. Execution plans generally aren't rocket science. And if someone messes up in writing their query, yes that should show up in query execution time stats. You don't need 10x anything to track average query times and spot outliers. For runtime-defined queries, those are obviously not going to have global indexes if there are more than a couple possible fields. But as long as you're only iterating over, say, 10K rows that an index can determine belong to that customer, and this query is happening once per page rather than 200 times per page, that's fine. That's why you can search over 10K bug reports for a project without a problem, because they only belong to a specific project. You're not searching all 100M bug reports belonging to all clients.
immibis 1y ago
People who are deeper into databases differentiate between OLTP and OLAP workloads. OLTP - on-line transaction processing - mostly consists of a finite set of queries that each access a small amount of data, like you when you pay a bill at a bank. OLAP - on-line analytical processing - consists of mostly summaries of large amounts of data which can be ad-hoc, like the banker who wants to know the total transactions for the day. The two kinds of workloads are very different - so much so that some systems even periodically export the whole transaction database and re-import into a separate analytics DBMS designed for OLAP work.

crazygringo 1y ago

It's not guesswork at all. It basically just requires knowing what it is allowed to look up rows by. Execution plans generally aren't rocket science. And if someone messes up in writing their query, yes that should show up in query execution time stats. You don't need 10x anything to track average query times and spot outliers. For runtime-defined queries, those are obviously not going to have global indexes if there are more than a couple possible fields. But as long as you're only iterating over, say, 10K rows that an index can determine belong to that customer, and this query is happening once per page rather than 200 times per page, that's fine. That's why you can search over 10K bug reports for a project without a problem, because they only belong to a specific project. You're not searching all 100M bug reports belonging to all clients.

immibis 1y ago

People who are deeper into databases differentiate between OLTP and OLAP workloads. OLTP - on-line transaction processing - mostly consists of a finite set of queries that each access a small amount of data, like you when you pay a bill at a bank. OLAP - on-line analytical processing - consists of mostly summaries of large amounts of data which can be ad-hoc, like the banker who wants to know the total transactions for the day. The two kinds of workloads are very different - so much so that some systems even periodically export the whole transaction database and re-import into a separate analytics DBMS designed for OLAP work.