Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Cases where full scans are better than indexes

jefftk.com

Post reply on HN
Print view View original

131–140 of 226 posts

Re: Cases where full scans are better than indexes

#131

Post by scott_w »

MaulingMonkey wrote: post #93
pkulak wrote: post #68

Just because the table scan is under some threshold doesn't automatically make it better. If a table scan takes 250ms vs 0.01 for a indexed lookup, you're still gonna have to justify to me why making silicon work that damn hard is worth even the electrical use. Are you inserting and deleting so many rows that maintaining the index is prohibitive? Do you have space concerns, and are not able to keep the index in memor…

Justify the dev time to save micro-pennies worth of electricity to me instead. A typical naive index won't help with my regular expression based queries, which aren't easily accelerated by an index. Or given an in-memory index, you've just increased memory use from O(1) to O(N), and I'll OOM on large files. Perhaps you'll throw a database at the problem, complicating I/O (especially when the data is generated/accesse…

> Justify the dev time to save micro-pennies worth of electricity to me instead.

The time spent justifying it is longer than the dev time itself. Any semi experienced engineer will throw basic indexes into their data model without even thinking and cover the most common use cases.

If they never use them… who cares? It took no time to add.

Re: Cases where full scans are better than indexes

#132

Post by winrid »

mumblemumble wrote: post #23
magicalhippo wrote: post #3

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

I used to be one of the "keep the database running" people at a high frequency trading firm. So, super high pressure; any outage meant we were potentially hemorrhaging money at a brain-melting pace. My lessons from that experience were twofold. First, you can't plan for sudden performance regressions ahead of time. The number of stars that have to align for that anticipatory index to actually help you are just too gr…

Are you just trying to create employment for yourself? This is amazingly bad advice. I literally get hired to fix these setups by people that take this approach.

Yes, it's good to periodically review query plans.

But designing and planning for data retrieval is part of a design process. And it's easy. Even Django since 2003 allowed you to define indexes inside your models. Got a 100m record table and you're adding a column you want to query by interatively? Rollout an index. This is normal day-to-day software development...

The write overhead is wild speculation. 99% of business are read heavy.

Re: Cases where full scans are better than indexes

#134

Post by hinkley »

jameshart wrote: post #22

“We’ll add an index when it gets slow” is saying “screw the guy who’s on call the night this system starts timing out”. Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering. If you deliberately aren’t implementing indexing, know at what point indexing would begin to matter. Put in place guard rails so the system doesn’t just blow up unexpectedly. Th…

As the number of cliffs increases, so does the likelihood that you will hit a second cliff in the middle of fixing the first one. At some point you hit a Kessler Syndrome situation where people start quitting and the people who should have fixed the problem in the first place aren't around anymore.

Nobody wants to stick around for a lecture they can see coming.

Re: Cases where full scans are better than indexes

#135

Post by raldi »

wswope wrote: post #71
jefftk wrote: post #53

Earlier quoted context omitted.

Consider the example from the post of searching your shell history. If I don't need indexes I can just have it all in one flat file and use grep. Switching to a tool that supports indexing adds a lot of potential complexity and ways things could go wrong. Or consider the example of a developer querying frontend logs: queries are many orders of magnitude less common than writes, and (at the scale I used to work at) an…

I take the point of your examples as written in the post, but I think both of those are a bad comparison to the Reddit Premium subscriber table being discussed, because: - We’re already using a database, so there’s very minimal added complexity - This is an extremely hot table getting read millions of times a day - The scale of the data isn’t well-bounded - Usage patterns of the table are liable to change over time

It wasn't an extremely hot table, and the scale of the data was well-bounded insofar as the only way for it to become non-negligible would be for us to have had revenue beyond our then-wildest dreams.

Re: Cases where full scans are better than indexes

#137

Post by yuliyp »

scott_w wrote: post #131
MaulingMonkey wrote: post #93

Earlier quoted context omitted.

Justify the dev time to save micro-pennies worth of electricity to me instead. A typical naive index won't help with my regular expression based queries, which aren't easily accelerated by an index. Or given an in-memory index, you've just increased memory use from O(1) to O(N), and I'll OOM on large files. Perhaps you'll throw a database at the problem, complicating I/O (especially when the data is generated/accesse…

> Justify the dev time to save micro-pennies worth of electricity to me instead. The time spent justifying it is longer than the dev time itself. Any semi experienced engineer will throw basic indexes into their data model without even thinking and cover the most common use cases. If they never use them… who cares? It took no time to add.

An RDBMS is not the best data store for all data. Sometimes flat files or other are the simplest tool for the job, as shown in the article. Adding a database to any of those tools would definitely not be worth the trade-off.

Re: Cases where full scans are better than indexes

#138

Post by perlgeek »

Slightly off topic, but just the other day we had a case where we had an index, and deleting ~100k entries from a table with a few, small columns caused a huge delay.

Context: Mariadb 10.2. In this table we stored traffic statistics, aggregated by IP address and day. Probably less than 200 bytes per row. They were also written per day, and then cleaned up by a cron job (every month an entire month worth of data was discarded), so "DELETE FROM traffic_stats WHERE day This worked fine on the primary, but our monitoring told us that the replicas started to lag, and showed that they spent inordinate amounts of time on the delete statement above. Even though we had an index on the "day" column.

Even weirder, the problem didn't show up in our QA database cluster, which had far less resources (vmware vm in QA vs. pretty solid bare metal in prod).

Even more puzzling, skipping the replication step and running the DELETE statement manually was quite fast (a second or so).

After some frantic search, it turned out that, for reasons lost in time, the QA db cluster used "mixed" replication, while the prod cluster used row-based replication. In row-based replication, the leader communicates to the replicas which rows were deleted. And since we didn't have a primary key on that table, the replicas did a full-table scan to find which record to delete, and repeated that for each of the 100k records to be deleted.

Adding a primary key to the table fixed the issue immediately, and we switched to mixed replication shortly after.

Re: Cases where full scans are better than indexes

#139

Post by eitally »

perlgeek wrote: post #138

Slightly off topic, but just the other day we had a case where we had an index, and deleting ~100k entries from a table with a few, small columns caused a huge delay. Context: Mariadb 10.2. In this table we stored traffic statistics, aggregated by IP address and day. Probably less than 200 bytes per row. They were also written per day, and then cleaned up by a cron job (every month an entire month worth of data was d…

I've very familiar with this sort of problem. I used to work in high-volume electronics manufacturing and coercing high & variable transaction OLTP databases to be able to be 1) backupable, and 2) replicable for analytics workloads often feels like a dark art. In a lot of cases, trial and error is a perfectly reasonable approach, for better or for worse.

Re: Cases where full scans are better than indexes

#140

Post by OliverJones »

I take the author's point for single-user write-frequently search-rarely use cases. Plenty of small greenfield projects look like that.

Bigger projects, or projects that are enjoying fast growth, may well need indexing. Why? to keep search performance up under a multi-user workload and/or a mixed write-query workload. Complex multi-table searches often also benefit from judicious indexing.

Here's something to keep in mind: Successful projects often find that an unpredicted query pattern has become very popular and/or a bottleneck. Often the bottleneck is remediated in production by adding appropriate indexes. SQL's designed to add indexing without changes to the data model, for precisely that reason. It's normal to add indexes as projects grow, and no sign of bad design or expensive technical debt. (Unsuccessful projects, or projects that don't need to grow? They don't have the problem.)

Post reply on HN