Skip to content

analytics: Add lang breakdown to code-intel analytics page

Administrator requested to merge codeintel-analytics-lang-breakdown into main

Created by: chrismwendt

Resolves https://github.com/sourcegraph/sourcegraph/issues/40345

CleanShot 2022-08-25 at 18 00 15

SQL query: https://explain.dalibo.com/plan/6DT#

CleanShot 2022-08-29 at 11 13 24

Query plan 
 HashAggregate  (cost=2864904.82..3193313.40 rows=81078 width=141) (actual time=3284.826..3285.506 rows=24 loops=1)
   Group Key: top_repos.repoid, events.lang, events.kind, CASE WHEN (events.name = 'codeintel.lsifDefinitions.xrepo'::text) THEN 'precise'::text WHEN (events.name = 'codeintel.lsifDefinitions'::text) THEN 'precise'::text WHEN (events.name = 'codeintel.lsifHover'::text) THEN 'precise'::text WHEN (events.name = 'codeintel.lsifReferences.xrepo'::text) THEN 'precise'::text WHEN (events.name = 'codeintel.lsifReferences'::text) THEN 'precise'::text ELSE 'search-based'::text END
   CTE events
     ->  Bitmap Heap Scan on event_logs  (cost=45138.31..2708237.58 rows=3243138 width=89) (actual time=422.293..3048.902 rows=209991 loops=1)
           Recheck Cond: (("timestamp" >= (now() - '8 days'::interval)) AND ("timestamp" <= now()))
           Filter: (CASE WHEN (name = 'codeintel.lsifDefinitions.xrepo'::text) THEN 'crossRepo'::text WHEN (name = 'codeintel.lsifDefinitions'::text) THEN 'precise'::text WHEN (name = 'codeintel.lsifReferences.xrepo'::text) THEN 'crossRepo'::text WHEN (name = 'codeintel.lsifReferences'::text) THEN 'precise'::text WHEN (name = 'codeintel.searchDefinitions.xrepo'::text) THEN 'crossRepo'::text WHEN (name = 'codeintel.searchReferences.xrepo'::text) THEN 'crossRepo'::text WHEN ((name = 'findReferences'::text) AND (source = 'CODEHOSTINTEGRATION'::text)) THEN 'codeHost'::text WHEN ((name = 'findReferences'::text) AND (source = 'WEB'::text)) THEN 'inApp'::text WHEN ((name = 'goToDefinition.preloaded'::text) AND (source = 'CODEHOSTINTEGRATION'::text)) THEN 'codeHost'::text WHEN ((name = 'goToDefinition.preloaded'::text) AND (source = 'WEB'::text)) THEN 'inApp'::text WHEN ((name = 'goToDefinition'::text) AND (source = 'CODEHOSTINTEGRATION'::text)) THEN 'codeHost'::text WHEN ((name = 'goToDefinition'::text) AND (source = 'WEB'::text)) THEN 'inApp'::text WHEN (name = 'codeintel.searchDefinitions'::text) THEN 'inApp'::text ELSE NULL::text END IS NOT NULL)
           Rows Removed by Filter: 3312451
           Heap Blocks: exact=322910
           ->  Bitmap Index Scan on event_logs_timestamp  (cost=0.00..44327.52 rows=3259435 width=0) (actual time=331.695..331.695 rows=3522442 loops=1)
                 Index Cond: (("timestamp" >= (now() - '8 days'::interval)) AND ("timestamp" <= now()))
   ->  Hash Join  (cost=81083.90..155653.77 rows=81078 width=100) (actual time=3215.029..3271.950 rows=24573 loops=1)
         Hash Cond: (events.repoid = top_repos.repoid)
         ->  CTE Scan on events  (cost=0.00..64862.76 rows=3243138 width=100) (actual time=422.297..445.307 rows=209991 loops=1)
         ->  Hash  (cost=81083.83..81083.83 rows=5 width=4) (actual time=2792.700..2792.702 rows=5 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Subquery Scan on top_repos  (cost=81083.77..81083.83 rows=5 width=4) (actual time=2792.691..2792.696 rows=5 loops=1)
                     ->  Limit  (cost=81083.77..81083.78 rows=5 width=12) (actual time=2792.689..2792.692 rows=5 loops=1)
                           ->  Sort  (cost=81083.77..81084.27 rows=200 width=12) (actual time=2792.688..2792.689 rows=5 loops=1)
                                 Sort Key: (count(1)) DESC
                                 Sort Method: top-N heapsort  Memory: 25kB
                                 ->  HashAggregate  (cost=81078.45..81080.45 rows=200 width=12) (actual time=2790.898..2791.969 rows=4765 loops=1)
                                       Group Key: events_1.repoid
                                       ->  CTE Scan on events events_1  (cost=0.00..64862.76 rows=3243138 width=4) (actual time=0.001..2724.752 rows=209991 loops=1)
   SubPlan 2
     ->  Index Scan using repo_pkey on repo  (cost=0.43..2.65 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=24)
           Index Cond: (id = top_repos.repoid)
   SubPlan 3
     ->  Index Only Scan using lsif_uploads_repository_id_commit_root_indexer on lsif_uploads  (cost=0.55..16.28 rows=19 width=0) (actual time=0.004..0.004 rows=1 loops=24)
           Index Cond: (repository_id = top_repos.repoid)
           Heap Fetches: 9
   SubPlan 4
     ->  Index Only Scan using lsif_uploads_repository_id_commit_root_indexer on lsif_uploads lsif_uploads_1  (cost=0.55..213911.11 rows=267273 width=4) (never executed)
           Heap Fetches: 0
 Planning Time: 0.795 ms
 Execution Time: 3296.456 ms

Test plan

Ran locally

Merge request reports