I mean SQL indexes generally speed up retrieving and slow down inserting. Does Salesforce indexes work in the same way as a SQL indexes?
For most part, yes, since there is no definitive article on this topic and the query execution optimization is abstracted to the end users or internally handled by Salesforce.
You can use the Query Plan Tool in Developer Console to explore the details of query plan and its execution details to some extent. If you run an expensive SOQL thru developer console multiple times and then observe the query plan details, you will notice that Salesforce chooses the fastest path or most efficient plan over a period of time.
Just would like to add some more points, straightforward to questions:
Does SOQL indexes generally speed up retrieving?
Yes, It does.Salesforce supports indexes (standard and custom) to speed up queries, and you can create custom indexes by contacting Salesforce Customer Support. https://developer.salesforce.com/docs/atlas.en-us.salesforce_large_data_volumes_bp.meta/salesforce_large_data_volumes_bp/ldv_deployments_infrastructure_indexes.htm
Does SOQL indexes generally slow down DMLS?
Additionally, indexes can add cost to DML and/or in some cases Query Optimizer needs to do things differently based on indexes which may impact performance
Hard to say for certain unless we get some input from Salesforce themselves, but we can say a few things...
Salesforce uses a collection of technologies (lots of Apache Software Foundation stuff) behind the scenes. HBase, Solr, Lucene, Phoenix, and some others if I recall. There was a session I attended at Dreamforce a few years back that went over those things at a high level. I'll try to find it again.
+edit: found it! The Future of Scale at Salesforce from Dreamforce '16. Around 11:30 in the video is when they start talking about their tech stack.
At the end of the day though, we end-users aren't too concerned about the details of how Salesforce is performing. They're a large entity and have teams dedicated to making sure they have the hardware and software to provide a fairly consistent and snappy level of service to their global customer base. I imagine that there are multiple datacenters that take incoming requests and divide them among available compute resources to build indices, and eventually get back to data consistency (i.e. perhaps favoring A and P of the CAP theorem, though that's just speculation)
Instead, what we are concerned about are the various governor limits. Things like:
- We can generally only query 50k rows per transaction
- We can only perform CRUD (via DML) on 10k rows per transaction
- We can perform 100 queries per transaction (200 if async)
And beyond that, our queries need to be "selective" (which is a topic all its own).
In the end, the benefit of indices are more apparent to us end-users (to help us adhere to the governor limits) than the costs of building and maintaining those indices.