181–190 of 226 posts
Earlier quoted context omitted.
Well, my guess is that updates and inserts are much more frequent than searches in their use case. You're assuming a balanced frequency for these operations and it hardly ever happens.
This is a read-heavy workload per the OP: https://news.ycombinator.com/item?id=36071799
Earlier quoted context omitted.
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.
Earlier quoted context omitted.
I'm not sure what you are trying to say, given the context of what you quoted was. Binary search has optimal worst case runtime complexity, but for small tables it is, on real computers, overall still slower than a linear scan, which effectively has the worst worst case runtime complexity (unless you start to get pathological). This is because the constant in front of the runtime complexity, the one that O-notation e…
> What exactly do you mean with "writing faster search and sort algorithms"? - Branchless binary search : https://news.ycombinator.com/item?id=35737862 / https://news.ycombinator.com/item?id=14598098 - Static B-Trees for faster binary search : https://news.ycombinator.com/item?id=30376140 Presumably there's even more literature on the topic.
But I see now you were referring to the branch predictor part specifically, so all good.
Moral of the post: don't do premature optimization. Common adage but it's a good reminder and example of it. Aside from one case where OP argued that the queries were so rare compared to the data updates that maintaining the index is more expensive. Which is also pretty classic when you're taught about indexes. What I recently learned the hard way about indexes is that they're slow when you have to read "large" chunk…
1. Cluster your data using the Gist/r-tree index. postgis docs [1] great explanation
2. Use the r-tree as covering index. IE add your associated data as dimensions into the index. The gist index becomes (lat, long, weather_c, avg_altitude), etc. This avoids having to load a separate page for each row in the spatial index [2]
[1]: https://postgis.net/workshops/postgis-intro/clusterindex.htm...
[2]: https://www.postgresql.org/docs/current/indexes-index-only-s...
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?
(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".
Earlier quoted context omitted.
> 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.
Earlier quoted context omitted.
> 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.
Because 90% of the time the RDBMS is.
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…
> If a table scan takes 250ms vs 0.01 for a indexed lookup I'm not sure whether the units of that latter number are still ms or now s or whatever, but either way isn't that where you are wrong? On real computers, there are lots of situations where linear access is trivially faster than a "better" data structure with theoretically better runtime complexity. 1000 accesses to a single page for a linear scan are going to…
It is infinitely easier to drop an index on a table that is causing poor performance than it is to add a good index to a table that suddenly needs one. Index everything, drop those that make things worse.
Deleting an index might be physically easy to do. But figuring out if it's safe to do is hard. You have to inspect every query you make and make sure none need that index. Once the index is there, in practice it's usually there forever.
“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…
Indices are far more likely to cause a cliff than to remove one, IME. Full table scans are linear and grow linearly. Indices are crazy fast until one day they're suddenly not.
I don't think I've ever seen such a cliff where the database is using the same query plan? There's sometimes small incremental slow downs as you pass btree depth thresholds and have to add more, but tbh even that's not usually noticeable.
I have seen plenty of instances where the database decides an index is no longer the right access,but gets that wrong, and the result is terrible.. but I see that as a different issue.