Is lookup skew bad if it doesn't bring about row lock contention?

Question

On Opportunity there are two lookups fields, the standard Primary Campaign Source, and a custom lookup to object Exhibition__c (which is set to Clear the value of this field if related record is deleted). So in theory, these should not cause any row lock issues, right? The company uses 1 campaign per year and one 1 Exhibition__c record per year with over 100k Opportunities. I am aware this is not best practice, but I am trying to understand the technical issues caused by this.

What will having this lookup skew produce? Is it simply processing time?

Answer 1

Aside from potential row lock issues (which could happen if a trigger on Opportunity causes an update to Campaign or Exhibition__c), the biggest practical issue that you'd run into here is that this makes the index on your lookup fields less useful.

Lookup fields are one of the field types that is automatically indexed by Salesforce, but if you have enough child records using a single parent then that index doesn't really help Salesforce in query planning. It harms (or rather, doesn't help) query selectivity, so any query you have which uses that lookup as a filter (as the only index filter) has a chance of running into the "Non-selective query against large object type (more than 200000 rows)" error.

Note that you don't need a single index to be used 200k times for this error to appear, just that the object at the base of your query (e.g. Opportunity) has more than 200k rows, and enough records related to a single parent record.