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

141–150 of 226 posts

Re: Cases where full scans are better than indexes

#141

Post by hinkley »

tomnipotent wrote: post #117
ComodoHacker wrote: post #81

Earlier quoted context omitted.

Modern DB engines can enforce Unique or PK constraints without indexes. Yes, they perform scans.

Every major RDBMS requires an index for both constraints.

FK constraints however are a pretty common gotcha. You have a table that allows deletes, and every table that has a column pointing to that table gets scanned on each delete operation.

So you have to add an index on that column, or start talking about tombstoning data instead of deleting it, but in which case you may still need the FK index to search out references to an old row to replace with a new one.

Re: Cases where full scans are better than indexes

#142

Post by jefftk »

doodlesdev wrote: post #74
jefftk wrote: post #60

Earlier quoted context omitted.

I was going to say that it was "instant", because that's how it feels when using it, but got 200ms from running "time" on grepping it to include a number in the post.

Have you tried out using ripgrep [0] or fzf [1]? 200ms is quite a lot for history search, I'd expect search to be 20ms or less. In fact, it should be instantaneous as it should all fit in memory really. I've been using fzf and the searches are all faster than my monitor can display frames (so less than 16ms), although I only have 10k entries on my history as I clear it up once in a while. [0]: https://github.com/Burn…

Trying now, "cat > /dev/null" takes ~150ms, so I suspect it's related to keeping my history in Google Drive (with local mirroring).

If it gets annoyingly slows I'll stop doing that and use a different syncing system.

Re: Cases where full scans are better than indexes

#143

Post by pornel »

lazide wrote: post #92
zht wrote: post #89

Earlier quoted context omitted.

can you do this (on a large table) without adding significant IO load/clogging up replication for an extended period of time?

It’s worth noting that if your DB instance is so heavily loaded that this is a real concern, you already have a huge problem that needs fixing.

It may be so loaded from all the full table scans it's doing.

Re: Cases where full scans are better than indexes

#144

Post by jefftk »

pkulak wrote: post #99
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. KEY (user_id) I mean, it's a dozen characters. Do you need to know how fast I type before you run the calculation?

(author)

If you're already using a relational database you should almost certainly set up indexes on your table ids and foreign keys. But that's pretty different from the examples in the post!

I'm not anti-index, I'm anti-"if you ever have a full table scan in production you're doing it wrong".

Re: Cases where full scans are better than indexes

#145

Post by wiredfool »

ksherlock wrote: post #54
ydnaclementine wrote: post #19

What is the rule that you use on which fields to index? I've been following the general when adding fields: if the field is mentioned in a sql WHERE, ORDER BY, or GROUP BY EDIT: JOINs

You should also consider how unique that field is. If half your widgets are blue and half your widgets are red, indexing the color is probably not helpful (ie, the query planner will ignore the widget_color index and do a full table scan). A rule of thumb number I vaguely recall is 10%

My recollection in postgres anyway is that low cardinality indexes aren't useful, because it doesn't take into account which side of the 1/99% you're on when determining to use the index.

What is useful is to do a partial index on values where foo=small % ,value because then it will use that index when it matches the query, but not when it's in the majority case.

Re: Cases where full scans are better than indexes

#146

Post by jameshart »

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…

Not advocating for building indexes for queries you might later want to run.

But for the queries you are building today? Don’t build them such that they will gradually slow down over time.

Re: Cases where full scans are better than indexes

#147

Post by jefftk »

jameshart wrote: post #105
jefftk wrote: post #55

Earlier quoted context omitted.

> screw the guy who’s on call the night this system starts timing out This was a very small billing practice, and that person was going to be me. I thought then, and still think now, that I made a reasonable trade off between what would be work at the time and potential future urgent work. Additionally, this wasn't the sort of thing that would fail suddenly when you hit a critical point. Instead, running full text se…

Stealing time from future you also doesn’t pay off. Future you wants to take vacations and change jobs and have family events and sleep and stuff. It doesn’t take much work to do the back of envelope math: How long should these queries take? Less than two seconds? How much slower do they get as record counts increase? 100ms every 250,000 records? Okay, so this will become intolerably slow when the record count hits a…

When building systems we are always making trade-offs between the present and the future. It's only "stealing time from future you" when you make bad trade-off; otherwise it's prioritization.

In this case, leaving out an index for full text search meant I didn't need to maintain the software for that index either, which would have been stand-alone at the time I was building this. Possibly this even was a choice that was, in expectation, time-saving for future me.

Re: Cases where full scans are better than indexes

#148

Post by jameshart »

magicalhippo wrote: post #46
themerone wrote: post #34

Earlier quoted context omitted.

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

Unless production loads are non-uniform in time, think Black Friday or similar.

Yup. Quarter end reporting has been going swimmingly for years but this time the orders query is hitting a connection timeout and the numbers are due tomorrow.

That threshold doesn’t gradually creep up on you, it hits you all at once at the worst possible time.

Re: Cases where full scans are better than indexes

#149

Post by cirrus3 »

It is never really mentioned why adding an index upfront is such a burden?

I don't really understand how the downside of adding an index almost ever outweighs the potential downsides of not adding an index. I'm sure there are some cases, and that was what I was expecting to see in this article, but it just seems to boil down to being so lazy that he wants to avoid typing a few more characters.

Re: Cases where full scans are better than indexes

#150

Post by nightpool »

mschuster91 wrote: post #95
lazide wrote: post #92

Earlier quoted context omitted.

It’s worth noting that if your DB instance is so heavily loaded that this is a real concern, you already have a huge problem that needs fixing.

AWS is particularly bad with their performance credit system on RDS... and there's to my knowledge no way to tell MySQL to limit index creation IOPS, which means in the worst case you're stuck with a system swamped under load for days and constantly running into IO starvation, if you forget to scale up your cluster beforehand. Even if the cluster is scaled to easily take on your normal workload, indexing may prove to…

I have never had any problems with CONCURRENT index creations under significant load using Postgres, fwiw
Post reply on HN