Skip to content

db: Improve performance of external service sync failure message

Warren Gifford requested to merge es/perf-extsvc-fail-msg into main

Created by: eseliger

This was one of the bad queries that we had in prod.

Before:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=59604.02..59604.14 rows=1 width=231) (actual time=95.943..98.616 rows=1 loops=1)
   ->  Gather Merge  (cost=59604.02..59619.66 rows=134 width=231) (actual time=95.942..98.613 rows=1 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=58604.00..58604.17 rows=67 width=231) (actual time=92.085..92.086 rows=1 loops=3)
               Sort Key: finished_at DESC
               Sort Method: top-N heapsort  Memory: 26kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Parallel Seq Scan on external_service_sync_jobs  (cost=0.00..58603.66 rows=67 width=231) (actual time=57.860..92.018 rows=17 loops=3)
                     Filter: ((external_service_id = 4129) AND (state = ANY ('{completed,errored,failed}'::text[])))
                     Rows Removed by Filter: 370732
 Planning Time: 0.202 ms
 Execution Time: 98.642 ms
(14 rows)

After:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=122.82..122.82 rows=1 width=231) (actual time=0.134..0.135 rows=1 loops=1)
   ->  Sort  (cost=122.82..123.22 rows=161 width=231) (actual time=0.134..0.134 rows=1 loops=1)
         Sort Key: finished_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Index Scan using erik_test_123 on external_service_sync_jobs  (cost=0.43..122.02 rows=161 width=231) (actual time=0.022..0.114 rows=52 loops=1)
               Index Cond: ((state = ANY ('{completed,errored,failed}'::text[])) AND (external_service_id = 4129))
 Planning Time: 0.100 ms
 Execution Time: 0.151 ms
(8 rows)

Test plan

Tested above, see query plans.

Merge request reports