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

121–130 of 226 posts

Re: Cases where full scans are better than indexes

#121

Post by tomnipotent »

electroly wrote: post #14
deepsun wrote: post #10

Earlier quoted context omitted.

Unless the small data changes often -- then DBMS needs to change both data and each index.

By definition, the changes will also be small. It's just not possible for it to blow up in a way that would cause a problem, because we're only talking about tiny tables. There's nothing you can do in a single[1] index for a table with 350 rows that will cause any heartache vs. an unindexed table with 350 rows. 4 of the 5 examples in the article are just "this table is too small to care." [1] Edited per discussion; a…

> By definition, the changes will also be small

Even if all you're updating is an int32 column from 1 to 2, the RDBMS is still writing much more data than 4 bytes. At a minimum it's making a copy of the full page the data is in prior to the update (so 8k for pg, 16k for mysql) and usually multiples of that (mysql writes both before/after).

Re: Cases where full scans are better than indexes

#122

Post by teen »

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…

not sure if this is trolling?

Re: Cases where full scans are better than indexes

#123

Post by kevan »

bob1029 wrote: post #116
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…

> Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering. I think this describes the general shape of problem seen in any engineering domain. After a certain point of time (i.e. unknown), we can no longer guarantee certain properties of the system. This is why we add all kinds of qualifiers and constraints in our discussions with the customer. Certain…

The key to sleeping at night is to add metrics and alarms near those cliffs or 'edges of the map' so they aren't invisible anymore. It's hard to anticipate every angle to this but in any case where you're making this kind of assumption or tradeoff it's a good idea.

A simple example is setting an alarm on your max load test traffic. When you get that alarm you know your system is now operating in unknown territory and it's probably time to do another load test or at least take a close look at scaling.

Re: Cases where full scans are better than indexes

#124

Post by jorblumesea »

bob1029 wrote: post #116
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…

> Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering. I think this describes the general shape of problem seen in any engineering domain. After a certain point of time (i.e. unknown), we can no longer guarantee certain properties of the system. This is why we add all kinds of qualifiers and constraints in our discussions with the customer. Certain…

Largely agree with this take, it often becomes "add an index because query might be slow" without much discussion or trade offs around query patterns. There's a lot of "what if" over engineering that happens where you end up in hypothetical scenarios.

Look at your real world use cases, query patterns and think hard about it.

Re: Cases where full scans are better than indexes

#125

Post by marcosdumay »

pixl97 wrote: post #87
themerone wrote: post #34

Earlier quoted context omitted.

Usually things will slow down gradually or fail right away under production loads.

W.U.C.T Works until critical threshold. In enterprise software I noticed software tends to work in a WUCT'ed up manner. Things may slow down over time, but no one complains about it because "the software is just slow", then suddenly one day you hit the timeout component of some higher layer and then the software is completely and utterly broke.

Well, this isn't a valid reason. It's not the software that is causing the WUCT.

If you let WUCT run free in any complex process, you are just asking for everything to break all the time and people to do nothing but fire-fighting. So, you are much better dealing with the WUCT causes instead of insisting that software or any other component scales forever? (Because they never will, and you will just make something else bream.)

WUCT is one of the main reasons why we monitor system operations.

Re: Cases where full scans are better than indexes

#129

Post by paxys »

I don't get it...they are just bragging about writing terrible code? There isn't a single reason given in the article about why a full scan is better than an index other than "I don't need these fancy index things, I'm perfectly happy with my 200ms query time".

Well add the index anyway and add an extra 195ms sleep() call if you really want to. It's still better for your hardware and electricity bill.

Re: Cases where full scans are better than indexes

#130

Post by TylerE »

MaulingMonkey wrote: post #110
pkulak wrote: post #99

Earlier quoted context omitted.

> Justify the dev time to save micro-pennies worth of electricity to me instead. KEY (user_id) I mean, it's a dozen characters. Do you need to know how fast I type before you run the calculation?

> Do you need to know how fast I type before you run the calculation? I'll assume 100WPM, call that two words, billed at $200/hour and call that $0.06, falling under "too cheap to be worth arguing against", which falls under the aforementioned: >> If it'a a 5 second "this is probably the right choice" kneejerk reaction, maybe it's fine. That said, there's a decent chance those 6 cents won't pay for themselves if this…

Now document the two words, run the test suite to verify no-breakagem commit to source control, and push to production.

Suddenly those two words cost a lot more than $0.06, and that's IF everything goes smoothly.

Post reply on HN