launchpad search gives timeout errors on specific searches

[Sorin Sbarnea]

This this search, which is not really complex one: https://bugs.launchpad.net/bugs/+bugs?field.searchtext=promotion+&search=Search&field.status%3Alist=NEW&field.status%3Alist=INCOMPLETE_WITH_RESPONSE&field.status%3Alist=INCOMPLETE_WITHOUT_RESPONSE&field.status%3Alist=CONFIRMED&field.status%3Alist=TRIAGED&field.status%3Alist=INPROGRESS&field.status%3Alist=FIXCOMMITTED&field.assignee=&field.bug_reporter=&field.omit_dupes=on&field.has_patch=&field.has_no_package=

You will likely get a something that looks like:

Timeout error
Sorry, something just went wrong in Launchpad.

We’ve recorded what happened, and we’ll fix it as soon as possible. Apologies for the inconvenience.

Trying again in a couple of minutes might work.

(Error ID: OOPS-e34fa5e478785fde5a8b0c78cfb26fcf)

I tried and error is persistent, other searches are giving similar errors but other are working.

[Colin Watson (cjwatson)]

Query plan from staging:

 Limit (cost=27.22..5475.19 rows=76 width=8) (actual time=315.823..3760.106 rows=76 loops=1)
   Buffers: shared hit=804261 read=346635
   InitPlan 1 (returns $0)
     -> Aggregate (cost=4.29..4.30 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)
           Buffers: shared hit=5
           -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.43..4.29 rows=2 width=4) (actual time=0.009..0.010 rows=2 loops=1)
                 Index Cond: (person = 1750507)
                 Buffers: shared hit=5
   InitPlan 2 (returns $2)
     -> Aggregate (cost=22.19..22.20 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)
           Buffers: shared hit=9 read=2
           -> Nested Loop (cost=0.85..22.18 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
                 Buffers: shared hit=9 read=2
                 -> Index Scan using teamparticipation_person_idx on teamparticipation teamparticipation_1 (cost=0.43..4.29 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)
                       Index Cond: (person = 1750507)
                       Buffers: shared hit=5
                 -> Index Only Scan using accesspolicygrant__grantee__policy__key on accesspolicygrant (cost=0.42..8.91 rows=4 width=8) (actual time=0.011..0.011 rows=0 loops=2)
                       Index Cond: (grantee = teamparticipation_1.team)
                       Heap Fetches: 0
                       Buffers: shared hit=4 read=2
   -> Nested Loop Left Join (cost=0.72..489672.33 rows=6831 width=8) (actual time=315.822..3760.061 rows=76 loops=1)
         Filter: ((bugtaskflat.product IS NULL) OR product.active)
         Rows Removed by Filter: 1
         Buffers: shared hit=804261 read=346635
         -> Index Scan Backward using bugtaskflat__importance__bugtask__idx on bugtaskflat (cost=0.43..479039.06 rows=7735 width=12) (actual time=191.217..3759.210 rows=77 loops=1)
               Filter: ((duplicateof IS NULL) AND ((fti)::tsvector @@ '''promot'''::tsquery) AND ((information_type = ANY ('{1,2}'::integer[])) OR COALESCE((access_grants && $0), false) OR COALESCE((access_policies && $2), false)) AND (status = ANY ('{10,13,14,20,21,22,25}'::integer[])))
               Rows Removed by Filter: 762329
               Buffers: shared hit=804178 read=346592
         -> Index Scan using product_pkey on product (cost=0.29..1.36 rows=1 width=5) (actual time=0.007..0.008 rows=1 loops=77)
               Index Cond: (bugtaskflat.product = id)
               Filter: active
               Rows Removed by Filter: 0
               Buffers: shared hit=83 read=43
 Total runtime: 3760.232 ms
(34 rows)

Other queries are indeed very much faster (sub-100ms), so there must be something wrong with BugTaskFlat.fti.