Cases where full scans are better than indexes
1–10 of 226 posts
Re: Cases where full scans are better than indexes
#2When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary.
It hadn’t happened by the time I left the company, even though sales were exceeding expectations.
Re: Cases where full scans are better than indexes
#3On the flip side, a missing index can bring down production.
We experienced that just a couple of weeks ago, where a missing index and an unexpected 1000x increase in volume at a customer brought the DB to its knees. Sure it was still serving queries but at such a low rate it was effectively useless.
For queries that will be run more than once, I try make sure there's an index it can use for something fairly unique.
Re: Cases where full scans are better than indexes
#4> I use grep on a flat file, and testing now it takes 200ms for a query across my 180k history entries
200ms is order of magnitude more than I'd prefer for interactive use
Re: Cases where full scans are better than indexes
#5When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary. It hadn’t happened by the time I left the company, even though sales were exceeding expectations.
Wow really? This seems really surprising to me.
Re: Cases where full scans are better than indexes
#6If the data is small, then the index will also be small, so it doesn't really matter either way. The reason to avoid indexes is if you can't accept the time or space cost. Only the last of OP's examples is a situation where choosing an index causes a problem; the rest are examples where an index isn't necessary but does not cause problems. Several examples are so trivial that you'd be crazy to use an external database at all. I'm not sure I'm really sold here.
Re: Cases where full scans are better than indexes
#7When I wrote Reddit Gold (now called Reddit Premium) I intentionally left the paying-user table index-free, looking forward to the day it would become necessary. It hadn’t happened by the time I left the company, even though sales were exceeding expectations.
What was the reasoning?
Re: Cases where full scans are better than indexes
#8CloudWatch Logs Insights, Snowflake, Grafana Loki all do minimally indexed queries with highly parallel brute force scans of tons and tons of smallish s3 objects. A lot of data, especially data like logs, the ratio of “retrieved:ingested” can be in excess of 1:100. That makes it very much worth while to not index up front but to pay the retrieval cost for that rare amount of data that is actually retrieved. The minimal indexing mentioned is typically time and sometimes bloom filteresque term existence indices at the object level.
Re: Cases where full scans are better than indexes
#9On the flip side, a missing index can bring down production. We experienced that just a couple of weeks ago, where a missing index and an unexpected 1000x increase in volume at a customer brought the DB to its knees. Sure it was still serving queries but at such a low rate it was effectively useless. For queries that will be run more than once, I try make sure there's an index it can use for something fairly unique.
Was this because of a missing index or because the optimizer vomited on the queries without an index to provide it statistics? My gripe with RDBs is generally the query optimizer is non deterministic with respect to the query, I.e., it can make a great decision with certain statistics and flip to a terrible one with slightly different statistics even though the original query would have performed basically the same.
I’d rather have a database that query performance degrade gracefully and predictably with scale than some sort of black magic mumbo jumbo wake me up in the middle of the night because it lost its statistical little puny pea brain simply because it couldn’t compute a bounded expectation any more.
Re: Cases where full scans are better than indexes
#10If the data is small, then the index will also be small, so it doesn't really matter either way. The reason to avoid indexes is if you can't accept the time or space cost. Only the last of OP's examples is a situation where choosing an index causes a problem; the rest are examples where an index isn't necessary but does not cause problems. Several examples are so trivial that you'd be crazy to use an external databas…
Unless the small data changes often -- then DBMS needs to change both data and each index.