3 ms·

I agree with other commenters that there's nothing new or surprising here [1] if you actually understand how indexes work. Which, if you're working in SQL, you

I agree with other commenters that there's nothing new or surprising here [1] if you actually understand how indexes work. Which, if you're working in SQL, you should. But the main takeaway, I disagree with. The author explains: > When I first set up Scour's database, I put a bunch of indexes on the items table without really thinking about whether they would help. For example, I had separate indexes on the published date, the language, and the quality rating. Useless. It's more important to have one or a small handful of good composite indexes on multiple columns than to have separate indexes on each column. Yes, the biggest error is throwing indexes at a table without having the slightest idea if they're helpful. But the idea that a smaller number of composite indexes is not the answer either. The answer is to go through every single query and make sure you have an index that matches the query, adding/changing indexes (including composite indexes) or rewriting queries as required. Indexes don't exist in a vacuum. They are optimizations for specific queries. Proper database design requires knowing exactly what information you will need to look up using which parameters, and designing the tables and indexes to produce that performantly. When you need to look up data in new ways, you often need to add new indexes or rearchitect tables entirely. [1] Except that the partial index match condition treats 0.9 and .9 as different. That is unexpected to me, but it is kind of in the docs at "The terms in W and X must match exactly": https://www.sqlite.org/partialindex.html https://www.sqlite.org/partialindex.html

DaiPlusPlus 1y ago
> 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.

DaiPlusPlus 1y ago

> 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.