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

11–20 of 226 posts

Re: Cases where full scans are better than indexes

#11

Post by twoodfin »

dmak wrote: post #5
raldi wrote: post #2

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

Speculating, but presumably this table only needed consultation during creation of a new user session. That’s probably a pretty heavyweight operation to begin with, so adding a scan of a few KB (user IDs being modestly sized integers) for every thousand currently paying users is NBD.

Re: Cases where full scans are better than indexes

#12

Post by londons_explore »

fnordpiglet wrote: post #8

CloudWatch 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 minim…

> CloudWatch Logs Insights, Snowflake, Grafana Loki

And the common thing in this list of tools? They're all laggy and not a nice experience to use.

Even if queries are rare, there is often an actual human sitting waiting for the results of a query. Whereas while ingesting logs, there is no human waiting. I would prefer to burn more CPU time to save some human time.

Re: Cases where full scans are better than indexes

#13

Post by raldi »

hardware2win wrote: post #7
raldi wrote: post #2

When 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?

I had read a Joel Spolsky post about how they used to get an email every time they made a sale (“Ding!”) and the day they finally turned it off was full of pride. (I did that too, originally not even filtered out of my inbox.)

Re: Cases where full scans are better than indexes

#14

Post by electroly »

deepsun wrote: post #10
electroly wrote: post #6

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

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 large number of indices on a small table can still get you in trouble.

Re: Cases where full scans are better than indexes

#15

Post by cactusplant7374 »

raldi wrote: post #2

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

Indexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.

Re: Cases where full scans are better than indexes

#17

Post by raldi »

twoodfin wrote: post #11
dmak wrote: post #5

Earlier quoted context omitted.

Wow really? This seems really surprising to me.

Speculating, but presumably this table only needed consultation during creation of a new user session. That’s probably a pretty heavyweight operation to begin with, so adding a scan of a few KB (user IDs being modestly sized integers) for every thousand currently paying users is NBD.

Nope; it was used every time we needed to look up whether someone was a subscriber, which was cached for some things but not all of them.

Re: Cases where full scans are better than indexes

#18

Post by nubinetwork »

Indexes got me from 15 seconds down to under a second. I'll always use them even if they wind up being as much space as the actual data. MySQL is just weird that way.

Edit: believe me, I tried it all... refining my queries, multiple tables, bigger hardware, faster disks, changing all the tuneables... nothing was as good as adding a whack-tonne of indexes.

Re: Cases where full scans are better than indexes

#20

Post by SoftTalker »

cactusplant7374 wrote: post #15
raldi wrote: post #2

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

Indexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.

They add overhead to updates and inserts. If you don't need them, why take that hit. Know your data.
Post reply on HN