12 ms·
Cases where full scans are better than indexes
- raldi 3y agoWhen 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.
- dmak 3y agoWow really? This seems really surprising to me.
- twoodfin 3y agoSpeculating, 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.
- hardware2win 3y agoWhat was the reasoning?
- raldi 3y agoI 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.)
- cactusplant7374 3y agoIndexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.
- SoftTalker 3y agoThey add overhead to updates and inserts. If you don't need them, why take that hit. Know your data.
- toast0 3y agoIf the table is small enough that indexes aren't critical for reads, they probably don't impact the rate of writes either. Of course, if the object is large scale bulk inserts, with only very occasional selects, then yes.
- EGreg 3y agoWait a second, the whole thing is you don't mind O(N) overhead in searching on every request, but you mind O(log N) overhead for updates and inserts?
- titaniczero 3y agoWell, 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.
- bcrosby95 3y agoSo no primary key or uniqueness constraints?
- ComodoHacker 3y agoModern DB engines can enforce Unique or PK constraints without indexes. Yes, they perform scans.
- tomnipotent 3y agoEvery major RDBMS requires an index for both constraints.
- hinkley 3y agoFK 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.
- tomnipotent 3y agoAn FK also adds the burden of keeping a copy of each related row during the lifespan of a transaction. This means small-but-frequently-updated tables that are joined to a lot can be a source of unexpected headaches.
- ComodoHacker 3y agoAfter checking the docs, you are right, I stand corrected. I was pretty sure Oracle and DB2 don't.
- Macha 3y agoUnlikely, given Reddit's past schema design. One table of "things", and then another table of attributes of those things in a entity,key,value format. https://kevin.burke.dev/kevin/reddits-database-has-two-tables/ https://kevin.burke.dev/kevin/reddits-database-has-two-table...
- eterm 3y agoThat goes against my intuition, because the performance would be more impacted (beneficial to have an index) where you have very few bits set on a boolean field. If you have 10m users and 100 have "IsPremium = 1" then an index massively speeds up anything with WHERE IsPremium = 1, compared to if the data is fairly evenly spread between IsPremium = 1 and IsPremium = 0 where an index won't be much benefit. So low sales would increase the need for an index. That said, I'm assuming searching for "Users WHERE IsPremium = 1" is a fairly rare thing, so an index might not make much difference either way.
- pc86 3y agoYou wouldn't need any of these WHERE clauses if you have all the premium users in one table. Even managing the users in both tables in pretty trivial when you just add someone as they pay and remove them as the premium account expires.
- deleted 3y ago[deleted]
- abtinf 3y ago> paying-user table index-free Presumably, that means they created a new table intended to be straight-joined back to the user table. No need to search a column.
- Vvector 3y agoJoining a table on a column without an index will require a linear scan of the column. You almost always want an index on JOIN columns
- lazide 3y agoThe primary key (userid?) will almost always be indexed implicitly. You’d usually have to go out of your way to avoid it. So it was probably being joined by an indexed column, but without an explicitly defined index.
- 3y ago
- BeefWellington 3y agoIt's not really index-free if a separate table has constraints of any kind.
- magicalhippo 3y agoOn 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.
- fnordpiglet 3y agoWas this because of a missing index or because the optimizer vomited on the queries without an index to provide it statistics? My gripe with RDBs is generally the query optimizer is non deterministic with respect to the query, I.e., it can make a great decision with certain statistics and flip to a terrible one with slightly different statistics even though the original query would have performed basically the same. I’d rather have a database that query performance degrade gracefully and predictably with scale than some sort of black magic mumbo jumbo wake me up in the middle of the night because it lost its statistical little puny pea brain simply because it couldn’t compute a bounded expectation any more.
- londons_explore 3y agoI really want the optimizer to make an estimate of the CPU/IO to complete a query. Then, during the query, if that much effort has been expended and we haven't yet completed the query, then update the estimate. If the updated estimate now shows that the query plan is no longer quickest, then abort the query and restart with a different plan. Years ago I forked postgres and tried to implement the above. Initial results were promising - there were a good chunk of queries that ended up taking a different plan, and sometimes returning 100x quicker. Alas, the postgres codebase is heindously complex, and implementing the above to be production grade would be many months work - and, due to the way postgres streams results to the client, might have actually required a change to the wire format.
- magicalhippo 3y agoIn this case it was a missing index, on a query run on every order line when saving aka a lot. It had gone under the radar because the volume with our other customers had been low enough that the table scan of the queried table wasn't noticed. As mentioned a customer suddenly got 1000x the volume and it quickly became an issue. But yea, we have a job running in the weekends to recalculate statistics on key tables, as we've had issues with that grinding production to a halt before. And recently I sped up a query by 100x by removing a filter from the where clause that for some weird reason caused the DB to run a really poor plan. It was just a simple check intended to filter out some duplicates in a few edge cases, but couldn't find a way to make the DB run it as a post-predicate. Moved it to my code for the 100x performance win...
- zokier 3y ago> I use grep on a flat file, and testing now it takes 200ms for a query across my 180k history entries 200ms is order of magnitude more than I'd prefer for interactive use
- fnordpiglet 3y agoYeah that was my thought too, and I thought about fishing out the unbranded $1 1Gb microsd in my drawer I use for 3d printing to help him out with his 0.5Gb storage challenge
- PhilipRoman 3y ago200ms for 180k entries sounds way too slow. Just tested on my 150k line log file and it takes about 10ms on average for various kinds of patterns with various hit frequencies. On an unrelated note, from a quick check with "strace", gnu grep seems to detect when output is redirected to /dev/null as no write syscalls are being made in that case.
- watermelon0 3y agoUnrelated note intrigued me, so I went looking in the source code; detection is here: https://git.savannah.gnu.org/cgit/grep.git/tree/src/grep.c#n2886 https://git.savannah.gnu.org/cgit/grep.git/tree/src/grep.c#n... Interesting that they would optimize for this use case.
- remram 3y agoEspecially since grep -q exists. It would never come to my mind to direct grep's output to /dev/null, I wonder how this optimization came to be?
- jefftk 3y agoI 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.
- electroly 3y agoIf 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 database at all. I'm not sure I'm really sold here.
- deepsun 3y agoUnless the small data changes often -- then DBMS needs to change both data and each index.
- electroly 3y agoBy 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.
- deepsun 3y ago> There's nothing you can do in an index for a table with 350 rows I meant if you have 100s of indices -- it might be slow even on small tables. I actually had a production issues from using a lot of indices, but it's not apples-to-apples with current discussion, because the table sizes, DBMS and update rates were much larger, fixed by removing indices and splitting table into multiple.
- electroly 3y agoWow! You're right; I wasn't considering that anyone would try to have that many indices. 100 is a lot. I should qualify my statement and say that you won't get in trouble with one index on a small table. I've edited my post accordingly.
- fnordpiglet 3y agoCloudWatch 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 minimal indexing mentioned is typically time and sometimes bloom filteresque term existence indices at the object level.
- londons_explore 3y ago> 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.
- fnordpiglet 3y agoI use all three and for large scales they’re considerably faster than the alternative; which at some scales (peta/exabyte) is “none.”
- SoftTalker 3y agoIndexing doesn't add much for very small tables. It also doesn't really help much if the cardinality of the data is low. I usually index any columns that are used in joins, or frequently used in predicates.
- nubinetwork 3y agoIndexes 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.
- ydnaclementine 3y agoWhat 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
- AnotherGoodName 3y agoDon't overthink that part. Just rely on EXPLAIN. It will tell you if the query had an index to use or if it scanned. You'd be surprised when a DB does/doesn't use an index if you try to figure it out yourself. A run book for any org when writing queries should be 1) Write the query 2) Before shipping the query run the query through EXPLAIN. Did it scan or did it use an index? 3) If it scanned can you re-write it to use an existing index (the usual answer honestly). 4) Create the index.
- ksherlock 3y agoYou 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%
- wiredfool 3y agoMy 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.
- anarazel 3y ago> 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. It does take that into account. Demo: =# CREATE TABLE low_cardinality AS SELECT generate_series(-10, 1000000) < 0 AS col; =# CREATE INDEX ON low_cardinality (col); =# ANALYZE low_cardinality; =# EXPLAIN SELECT * FROM low_cardinality WHERE col = false; ┌─────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────┤ │ Seq Scan on low_cardinality (cost=0.00..14425.11 rows=1000011 width=1) │ │ Filter: (NOT col) │ └─────────────────────────────────────────────────────────────────────────┘ (2 rows) =# EXPLAIN SELECT * FROM low_cardinality WHERE col = true; ┌────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Only Scan using low_cardinality_col_idx on low_cardinality (cost=0.42..4.44 rows=1 width=1) │ │ Index Cond: (col = true) │ └────────────────────────────────────────────────────────────────────────────────────────────────────┘ (2 rows)
- jameshart 3y ago“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. There are 100% cases where you shouldn’t use indexes. That’s not the same thing as being able to get away without an index.
- themerone 3y agoUsually things will slow down gradually or fail right away under production loads.
- magicalhippo 3y agoUnless production loads are non-uniform in time, think Black Friday or similar.
- jameshart 3y agoYup. 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.
- david422 3y agoBut when you have a 100 places all adding little slowdowns it can be difficult to realize.
- pixl97 3y agoW.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.
- captainmuon 3y agoThe inverse is also true, often you have linear or even quadratic lookup times when something like an index can give you the result "immediately". I wish languages other than SQL had a concept of adding an index. Imagine a variant of Python's list or C++'s vector where you can add indicies on arbitrary fields of the contents, like a more flexible dict. Something like (pseudocode) books = List<Book, indices=[Book.title, Book.author]> and then you could up by title or author without traversing the whole list. Sure you can just use a separate dict, but then you have to keep both in sync, it is harder to bind it to UI controls, and so on.
- magicalhippo 3y agoFor C++ there's Boost's multi_index[1]. [1]: https://www.boost.org/doc/libs/1_82_0/libs/multi_index/doc/index.html https://www.boost.org/doc/libs/1_82_0/libs/multi_index/doc/i...
- Mailtemi 3y agoI used multi_index a lot in the past. However, since I also frequently use sqlite, I have decided to exclusively use SQLite for multi_index in memory. If there is a problem, temporarily using sqlite as a file makes it a lot easier to track the issue. When multi_index patterns become too complex, it's natural to wrap them in a unit test specifically designed for sqlite (multi_index).
- jmcomets 3y agoIn my experience these type of abstractions end up bloated with too many concerns which adds maintenance overhead. It's a lot simpler to simply add a `HashMap<Book.title, Book>` and cover with tests, than use an abstraction. I want languages to do less magic, not more, since I read code more than I write it.
- PaulHoule 3y agoOne of the problems of the RDF and SPARQL world is that the standard index structure considers the 6 possible permutations of ?subject ?predicate ?object. You can answer many queries with good efficiency with those indexes but building them gets brutal when you are handling billions of triples. I went to a talk at Dreamforce years ago where the CTO explained their database architecture and it is interesting that the core table in Saleforce is literally a collection of triples. They have a smart optimizer that builds views, materialized views, indexes, etc. in Oracle based on the queries you run. I built a bot that would download a lot of records from an organization, build a "machine learning" model (built by a geospatial analyst and we didn't call it that these days) that automatically assigns opportunities to salespeople, and then pushes the assignments into the system. The first time I ran the big query in the test system it timed out, then I went to the bathroom and when I came back the second time it worked perfectly. When it went to production exactly the same thing happened.
- cdcarter 3y ago> it is interesting that the core table in Saleforce is literally a collection of triples This is... not entirely true. Most of the data for each "Object" in salesforce are stored in a series of columns on a single table. These are called "flex columns" in the official whitepaper describing the architecture. [0] However, _foreign key relationships_ are indeed stored in a table that acts like a collection of triples. This is used in place of native foreign keys, and is indeed deeply integrated with the optimizer. [0]: https://www.developerforce.com/media/ForcedotcomBookLibrary/Force.com_Multitenancy_WP_101508.pdf https://www.developerforce.com/media/ForcedotcomBookLibrary/...
- pyrophane 3y agoI think the risk of creating indexes prematurely is really that you will make assumptions about how your table is going to be queried that don't turn out to be correct, so you create indexes that you will never need, even if the table gets large. That probably isn't the end of the world, but it can make it harder for others to understand what is going on in your database (indexes can also be a form of documentation) and could make your ORM code somewhat more complex. The worst case scenario, I suppose, would be you create an index on a table that grows large
- giraffe_lady 3y agoThis has matched my experience too. Most experienced developers can probably identify an inappropriately hot table and add an index to it. Where a DB with dozens of individually modest indexes that add up to be huge is much trickier and more labor intensive to confidently improve.
- jmcomets 3y agoFWIW, most DBMS have built-in index usage stats, and it's not too difficult to query. In my previous job we had a Postgres health dashboard that notably showed `log10(index size) / index hits`, making it pretty clear when some recently added index was useless. My opinion is that indexes should be either logical (ex: used for exclusion constraints) or purely for performance (tradeoff space for better QPS). Query patterns change, specs change, so monitoring your DB's performance is the way to go.
- jacobsenscott 3y agoMost databases will ignore indexes if the table is small enough, so it sort of does this for you. So the index can just be a waste of space, until it isn't.
- pkulak 3y agoJust 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 memory, or maybe even on disk? The Bash history thing makes sense, because indexing is a lot of work. But otherwise, just use an index and move on to the next problem. EDIT: Has anyone else forgotten to add an index, then noticed the performance regression a year later? That's always fun, because now adding the index could mean downtime, and even knowing if or how much is difficult because putting that much data somewhere else to test isn't trivial. No thank you.
- leetrout 3y agoAgree but you can always add an index without downtime. It just becomes a money and complexity issue ;)
- zht 3y agocan you do this (on a large table) without adding significant IO load/clogging up replication for an extended period of time?
- lazide 3y agoIt’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.
- mschuster91 3y agoAWS 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 be too much for IO burst credits
- mastax 3y ago> I recently came across someone maintaining a 0.5GB full text index to support searching their shell history, 100k commands. I use grep on a flat file, and testing now it takes 200ms for a query across my 180k history entries. I just started using a tool that stores my bash history and replaces C-R that I found on HN, it's written in Rust and uses SQLite of course. But it'll randomly cause commands to pause for a few seconds before executing (I think the hook to write the command to history gets blocked). Probably some simple bug, but I could just have my command history in a flat file and use rg or fzf in a 5 line shell function and have no lag.
- scarmig 3y agoCan you share the tool? I've been thinking of making something similar for fun backed by a suffix array, interested in what else is out there.
- wswope 3y agohttps://github.com/ellie/atuin https://github.com/ellie/atuin
- mastax 3y agoOkay getting real off-topic now (welcome to HN) but this is not what I expected: https://github.com/ellie/atuin/issues/952#issuecomment-1537884120 https://github.com/ellie/atuin/issues/952#issuecomment-15378... https://github.com/openzfs/zfs/issues/14290 https://github.com/openzfs/zfs/issues/14290 Apparently when SQLite calls `ftruncate` on the `-shm` file, ZFS will sometimes block for several seconds. Maybe it's waiting for txg timeout? Strange.
- XCSme 3y agoWhy not just add the index? It future-proofs the performance and, as mentioned in the article, for small datasets, performance is not an issue anyway, so adding an index won't affect the small-dataset performance.
- jasfi 3y agoI agree, you don't want performance to suddenly degrade and then you have to find out why. This can be especially bad when this happens with many tables that should have indexes, and performance is sluggish, but not for any specific action. Add indexes appropriately is a best practice.
- vivegi 3y agoOnce upon a time (25+ years ago) I used to maintain an Oracle database that had around 50M records in its main table and 2M records in a related table. There were a dozen or so dimension (lookup) tables. Each day, we would receive on the order of 100K records that had to be normalized and loaded into database. I am simplifying this a lot, but that was the gist of the process. Our first design was a PL/SQL program that looped through the 100K records that would be loaded into a staging table from a flat file, performing the normalization and inserts into the destination tables. Given the size of our large tables, indices and the overhead of the inserts, we never could finish the batch process in the night that was reserved for the records intake. We finally rewrote the entire thing using SQL, parallel table scans, staging tables and disabling and rebuilding the indexes and got the entire end-to-end intake process to finish in 45 minutes. Set theory is your friend and SQL is great for that.
- zvmaz 3y ago> Set theory is your friend and SQL is great for that. Could you tell us how set theory was useful in your case?
- wswope 3y agoThe implication is that the PL/SQL job was operating rowwise. Batch processing, backed by set theory in this case, is far more efficient than rowwise operations because of the potential for greater parallelism (SIMD) and fewer cache misses. E.g., if inserting into a table with a foreign key pointing to the user table, batch processing means you can load the user id index once, and validate referential integrity in one fell swoop. If you do that same operation rowwise, the user id index is liable to get discarded and pulled from disk multiple times, depending on how busy the DB is.
- lazide 3y agoAlso, each insert will have to acquire locks, verify referential integrity, and write to any indexes individually. This is time consuming.
- 3y ago
- slaymaker1907 3y agoTiddlywiki only uses an indices for tags/fields yet it still works pretty well so long as the wiki isn't huge. There's something to be said for the flexibility an approach like that provides.
- grumple 3y agoThe HN title says something different than the blog post title / intent. The HN title is incorrect and misleading imo. The blog post can be summarized as "you don't need indexes on tiny tables".
- vertica3525 3y ago[flagged]
- CathalMullan 3y agoLately, I've been investigating 'serverless' databases (PlanetScale, Turso) that charge based on the number of rows read/scanned. As a result, instead of just relying on the perceived performance of a query, I've started monitoring the number of rows scanned, which has emphasized the importance of indexes to me. Granted, the cost per rows read is minuscule (PlanetScale charges $1 per billion!), but it's still something I keep in mind.
- hamilyon2 3y ago1) Postgres optimizer at some point prefers full scans. It depends on your data, your query and configuration. So on read-heavy load you basically lose nothing by adding all appropriate indexes from the get-go. Overhead having index on small tables is small. 2) If your load is write-heavy, you should consider trade-offs. Yes, full scans are sometimes your friends. Interesting thing to notice here that write amplification is basically same even if table is small.
- chubot 3y agoAnyone remember gsearch by Jeff Dean circa 2006 at Google? It was a full scan regex search, using 40-80 machines in parallel, not an index Also sourcerer was a full scan of VCS metadata, on a single machine. i.e. if you listed all the commits by a certain person, it would just do a for loop in C++ going through about 1-2 M commits. They were just C++ structs in memory, and it was probably submillisecond times, even with hardware at the time I remember reading those 2 programs and being impressed that they were both very fast, and full scans! (gsearch got slow later as the codebase grew, but the first version was a <1 second distributed grep of the whole Google codebase.)
- syngrog66 3y agorule of thumb: O(n) latency is almost always fine when (n==1)
- mrguyorama 3y agoIt 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.
- grogers 3y agoAdding the index may be physically hard because it takes a while and steals resources from your server. But cognitively, it is easy - find the query that is slow and add the index to make it fast. It's usually very obvious from the query what index you need to add 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.
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- paxys 3y agoI 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.
- mrits 3y agoWhile I'm not sure why you moved their recently I can see why you wouldn't have noticed how nice it used to be. As I said, it already started the decline a decade ago.
- winrid 3y agoDon't add indexes. Add them later when things are slow and everything is on fire! Source: guy that gets hired to fix database stuff on fire.
- deleted 3y ago[deleted]
- perlgeek 3y agoSlightly 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.
- eitally 3y agoI'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.
- OliverJones 3y agoI 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.)
- cirrus3 3y agoIt 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.
- taeric 3y agoI feel this somewhat generalizes. "Cases where a simple list/array is better than a hashed collection." Which, oddly has the perverse problem of "when you should give up on a single collection of your data, and embrace storing it in different ways." That is, few folks seem to internalize or consider that every index is essentially a copy of the data. A complete copy, if you are doing a fully projected index. So, when is it better to just do a full scan over something else? Well, its probably never better in isolation of every other concern. But, when is it better to just do a scan of the data over duplicating it in a place that would let you hash in? Depends on how much extra work you are doing because of it? Having typed that, really I think the trick is to ask yourself roughly (not exactly, amusingly) what it means to add an index. Or to use a hashtable. Or to use a list and a hashtable. And realize that the question of using an index is the same as the question of keeping another copy of your data somewhere.
- Aachen 3y agoMoral 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" chunks of a table to find an answer, in my case computing a median for an area. I'm indexing geospatial data and Europe+NA are overrepresented. When you view an area the size of ~Germany, the query would take 20 minutes and compute the median over ~3% of all records whereas a full table scan (whole world) took something like 40 seconds (with the median being computed over the same 3%, it would just evaluate the WHERE clause against every row instead of finding rows to include via the index). That's the power of a sequential read as compared to reading an Rtree. I haven't had such bad experiences with Btree indexes, not sure if that would behave just as badly. On the other hand, if you ask for an area the size of Amsterdam, the index is normal fast, and for <50 rows it's basically instant whereas the full table scan would still take the same 40 seconds.
- iLoveOncall 3y agoAdding an index is an incredibly expensive task on a dataset so big that it needs one to be added. It's something that is likely to require massive engineering effort on a live system. Removing an index, on the other hand, is never an issue. It's not at all premature optimization, it's simply basic software design.
- Aachen 3y agoRead OP's post please. They're talking of systems where it was acceptable to always do full table scans, in one case having 350 rows. Not "an incredibly expensive task on a dataset so big that it needs one". Thus I read OP's post as recommending to not do premature optimization (without using those words literally).
- srcreigh 3y agoThe cause of this is the likely N+1-like behaviour of indexes on un-clustered data. Two options for speeding this up a ton (they dont' make sense to use together), 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.html https://postgis.net/workshops/postgis-intro/clusterindex.htm... [2]: https://www.postgresql.org/docs/current/indexes-index-only-scans.html https://www.postgresql.org/docs/current/indexes-index-only-s...
- jeffreyrogers 3y agoHe didn't show that full scans are better than indexes, just that they aren't obviously worse when the data is small.
- joshu 3y ago"fits in ram" ok got it
- thesmart 3y agoSaying there are cases where full scan is better than an index is really just describing an edge case where the default index implementation is non-optimal. There are edge cases where default implementation of an index will not result in good performance in some use cases. For example, write heavy loads where the updated tuple values are frequently changing position in the index constantly. However, the solution is rarely "no index". A solution is to throttle the index update by an acceptable delta through a non-default implementation. A solution is to develop your own application-side index. A solution is a custom PSQL function that interacts with an index in a more complicated way. Maybe a non-default implementation is not priority right now, e.g. if scale is low. But it is always good to think ahead and to have a plan and to log the tech debt and track it responsibly so there are no surprises later. Don't build on shifty foundations.