+filebug-show-similar FTI query timeouts

https://lp-oops.canonical.com/oops.py/?oopsid=1662A16

SQL time: 17030 ms
Non-sql time: 816 ms
Total time: 17846 ms
Statement Count: 28

https://bugs.launchpad.net/ubuntu/+filebug

The query in that oops is:
SELECT COUNT(CASE WHEN Bug.fti @@ ftq('sphinx') THEN TRUE ELSE null END) FROM Bug, BugTask WHERE BugTask.bug = Bug.id AND BugTask.distribution = 1;
which is terrible

AFAIK its equivalent to
SELECT COUNT(*) from Bug, bugtask where bug.fti @@ ftq('sphinx') and BugTask.bug = Bug.id AND BugTask.distribution = 1;
which is tolerable

lpmain_staging=> explain analyse SELECT COUNT(*) from Bug, bugtask where bug.fti @@ ftq('sphinx') and BugTask.bug = Bug.id AND BugTask.distribution = 1;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=5385.74..5385.75 rows=1 width=0) (actual time=481.898..481.899 rows=1 loops=1)
   -> Nested Loop (cost=110.93..5384.66 rows=432 width=0) (actual time=333.412..481.830 rows=40 loops=1)
         -> Bitmap Heap Scan on bug (cost=110.93..1841.57 rows=600 width=4) (actual time=333.366..480.218 rows=101 loops=1)
               Filter: ((fti)::tsvector @@ '''sphinx'''::tsquery)
               -> Bitmap Index Scan on bug_fti (cost=0.00..110.78 rows=600 width=0) (actual time=323.701..323.701 rows=10819 loops=1)
                     Index Cond: ((fti)::tsvector @@ '''sphinx'''::tsquery)
         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..5.89 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=101)
               Index Cond: (bugtask.bug = bug.id)
               Filter: (bugtask.distribution = 1)
 Total runtime: 482.213 ms
(10 rows)

Time: 489.949 ms
lpmain_staging=> explain analyze SELECT COUNT(CASE WHEN Bug.fti @@ ftq('sphinx') THEN TRUE ELSE null END) FROM Bug, BugTask WHERE BugTask.bug = Bug.id AND BugTask.distribution = 1;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=281594.13..281594.14 rows=1 width=386) (actual time=9579.732..9579.733 rows=1 loops=1)
   -> Hash Join (cost=201501.09..280513.73 rows=432158 width=386) (actual time=3051.529..6739.112 rows=436649 loops=1)
         Hash Cond: (bugtask.bug = bug.id)
         -> Seq Scan on bugtask (cost=0.00..37077.68 rows=432158 width=4) (actual time=0.031..562.978 rows=436649 loops=1)
               Filter: (distribution = 1)
         -> Hash (cost=163550.93..163550.93 rows=599693 width=390) (actual time=3051.151..3051.151 rows=599696 loops=1)
               -> Seq Scan on bug (cost=0.00..163550.93 rows=599693 width=390) (actual time=0.010..1249.606 rows=599696 loops=1)
 Total runtime: 9579.850 ms
(8 rows)

Time: 9582.806 ms

nl_phrase_search is deliberately examining the ft index of each indexed item row by row: this is hugely slow and unscalable, for dubious results: doing an experiment now to discard this.

Fixed in stable r11199 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11199>.

There is more to do here, its not done yet.

So, this is now in stable and looking pretty good to me. Still room to improve, have generated a custom oops and will look at it tomorrow.

Fixed in stable r11277 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11277>.

Fixed released in launchpad-project 10.08.

Fixed in stable r11557 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11557>.

Fixed in stable r11582 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11582>.