4 ms·

> It's not the database's fault for not being able to write 10M rows to the disk quickly. I am primarily saying this in regards to reads, but sometimes the dat

> It's not the database's fault for not being able to write 10M rows to the disk quickly. I am primarily saying this in regards to reads, but sometimes the database management system is indeed at fault, by picking sub-optimal ways to do things. For example, I had an Oracle query that took 45 minutes to execute and I was called in to help out with it. This drastic slowdown hadn't been alleviated by reviewing what indices are in use, nor partitioning the table, however I did a bit of research and was able to optimize the execution to 3 seconds, with the NO_UNNEST optimizer hint: https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm#26730 https://docs.oracle.com/cd/B12037_01/server.101/b10752/hints... for an EXISTS check. Thankfully cases like that are somewhat rare, but I'd still generally pick boring and battle tested DBMSes for most of my use cases (maybe along the lines of PostgreSQL and MariaDB for a variety of reasons, or even SQLite for simpler cases), as opposed to newer ones. But when dealing with performance considerations, then it indeed is a good idea to look at whatever metrics are available, to see whether it's the hardware or the software that's responsible for certain characteristics. Of course, for some workloads Redis or RabbitMQ might be better suited, if available (though I've seen key-value stores and message queues implemented in SQL that worked fine, at least up to a point).

ludston 4y ago
If you have queries with joins or subqueries, and you have filters on multiple tables in the query, the DBMS has to make a best guess at order in which to execute the query. If you are joining, for example, two very large tables and there is a filter on each, if the first table has 10 matching rows and the second table has 1 billion, you would hope the execution plan will filter by the first table first, but the DBMS may not know how many rows match each filter unless there are appropriate column statistics, so it may execute the query in the other order. 3 seconds is often horrible performance for an sql query (although it may be "good enough" for your use-case), and implies hundreds of thousands or millions of comparisons during execution, which implies that the query is not covered well by indexes. In short, if you understand the shape of the data in the DBMS better than the database does, then you ought to provide index hints and order hints so that it can do as well as it can. (And if this particular multi-table filter is something that is run by users multiple times a minute, you should be investigating multi-table indexes, denormalising tables, or some third party external index like cdc into lucene.

ludston 4y ago

If you have queries with joins or subqueries, and you have filters on multiple tables in the query, the DBMS has to make a best guess at order in which to execute the query. If you are joining, for example, two very large tables and there is a filter on each, if the first table has 10 matching rows and the second table has 1 billion, you would hope the execution plan will filter by the first table first, but the DBMS may not know how many rows match each filter unless there are appropriate column statistics, so it may execute the query in the other order. 3 seconds is often horrible performance for an sql query (although it may be "good enough" for your use-case), and implies hundreds of thousands or millions of comparisons during execution, which implies that the query is not covered well by indexes. In short, if you understand the shape of the data in the DBMS better than the database does, then you ought to provide index hints and order hints so that it can do as well as it can. (And if this particular multi-table filter is something that is run by users multiple times a minute, you should be investigating multi-table indexes, denormalising tables, or some third party external index like cdc into lucene.