Skip to content

codeintel: Improve lock contention in insertSearchRecords

Warren Gifford requested to merge ef/current-lock-contention into main

Created by: efritz

This PR improves (hopefully) the last remaining lock contention in the write-heavy path of LSIF upload processing. This changes the insertSearchRecords function to instead of overwriting a shared row in the database, to simply insert it with a monotonically increasing value.

This allows us to have the LSIF upload path be insert-only (no contention on rows), and also allows us to (fairly trivially) alter how we keep track which search records are current and which should not be used for user requests. @slimsag Here's how I think you should make efficient queries:

explain analyze 
select * 
from lsif_data_docs_search_public s 
where s.dump_id = (
    select c.dump_id 
    from lsif_data_docs_search_current_public c 
    -- find matching records in current table
    where 
        c.repo_id = s.repo_id and 
        c.dump_root = s.dump_root and 
        c.lang_name_id = s.lang_name_id 
    -- select only newest one
    order by c.created_at desc, id limit 1
) and
-- just to avoid seq scan over all lsif_data_docs_search_public 
-- (we'd be filtering by _something_ here in practice)
repo_id = 1;

Here is a sample query plan, which uses index-only scans over a much smaller table than what's stored in lsif_data_docs_search.

                                                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using lsif_data_docs_search_public_repo_id_idx on lsif_data_docs_search_public s  (cost=0.29..5.80 rows=1 width=730) (actual time=0.008..0.009 rows=0 loops=1)
   Index Cond: (repo_id = 1)
   Filter: (dump_id = (SubPlan 1))
   SubPlan 1
     ->  Limit  (cost=0.14..1.49 rows=1 width=12) (never executed)
           ->  Index Only Scan Backward using lsif_data_docs_search_current_public_lookup on lsif_data_docs_search_current_public c  (cost=0.14..8.27 rows=6 width=12) (never executed)
                 Index Cond: ((repo_id = s.repo_id) AND (dump_root = s.dump_root) AND (lang_name_id = s.lang_name_id))
                 Heap Fetches: 0
 Planning Time: 0.138 ms
 Execution Time: 0.025 ms
(10 rows)

Helps #21938

Merge request reports