TemporaryStorageManager:CollectionResource:#temporary_blobs timeouts

[Robert Collins]

 15 SELECT TemporaryBlobStorage.date_created, TemporaryBlobStorage.file_alias, TemporaryBlobStorage.i ... T $INT:$INT:$INT.$INT+$INT:$INT ORDER BY TemporaryBlobStorage.date_created LIMIT $INT OFFSET $INT:
   GET: 15 Robots: 0 Local: 0
     15 https://api.launchpad.net/1.0/temporary-blobs (TemporaryStorageManager:CollectionResource:#temporary_blobs)
       OOPS-1893B334, OOPS-1893C884, OOPS-1893D920, OOPS-1893E10, OOPS-1893F898

[Robert Collins (lifeless)]

launchpad_qastaging=> explain analyze SELECT TemporaryBlobStorage.date_created,
       TemporaryBlobStorage.file_alias,
       TemporaryBlobStorage.id,
       TemporaryBlobStorage.uuid
FROM LibraryFileAlias,
     TemporaryBlobStorage
WHERE TemporaryBlobStorage.file_alias = LibraryFileAlias.id
  AND LibraryFileAlias.expires > '2011-03-08 04:28:14.106317+00:00'
ORDER BY TemporaryBlobStorage.date_created LIMIT 76 OFFSET 0;
                                                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=10724.21..10724.40 rows=76 width=53) (actual time=18095.300..18095.300 rows=0 loops=1)
   -> Sort (cost=10724.21..10743.39 rows=7672 width=53) (actual time=18095.298..18095.298 rows=0 loops=1)
         Sort Key: temporaryblobstorage.date_created
         Sort Method: quicksort Memory: 25kB
         -> Hash Join (cost=595.39..10446.18 rows=7672 width=53) (actual time=18095.264..18095.264 rows=0 loops=1)
               Hash Cond: (libraryfilealias.id = temporaryblobstorage.file_alias)
               -> Index Scan using libraryfilealias__expires__idx on libraryfilealias (cost=0.00..9668.58 rows=7672 width=4) (actual time=59.632..17987.381 rows=7550 loops=1)
                     Index Cond: (expires > '2011-03-08 04:28:14.106317'::timestamp without time zone)
               -> Hash (cost=375.73..375.73 rows=17573 width=53) (actual time=88.785..88.785 rows=17573 loops=1)
                     -> Seq Scan on temporaryblobstorage (cost=0.00..375.73 rows=17573 width=53) (actual time=6.401..72.630 rows=17573 loops=1)
 Total runtime: 18095.552 ms
(11 rows)

[Robert Collins (lifeless)]

Missing index I think. Stuart - what do you think ? (and if it is, can you add it?)

[Robert Collins (lifeless)]

alternatively, it might be a noddy sort - the LFA id should be the same order as the date_created and is on the same table as the filter. It gives this plan:

cold:

                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..1287.98 rows=76 width=57) (actual time=14919.071..14919.071 rows=0 loops=1)
   -> Nested Loop (cost=0.00..130018.16 rows=7672 width=57) (actual time=14919.069..14919.069 rows=0 loops=1)
         -> Index Scan using temporaryblobstorage_file_alias_key on temporaryblobstorage (cost=0.00..1016.83 rows=17573 width=53) (actual time=13.975..57.659 rows=17573 loops=1)
         -> Index Scan using libraryfilealias_pkey on libraryfilealias (cost=0.00..7.33 rows=1 width=4) (actual time=0.845..0.845 rows=0 loops=17573)
               Index Cond: (libraryfilealias.id = temporaryblobstorage.file_alias)
               Filter: (libraryfilealias.expires > '2011-03-08 04:28:14.106317'::timestamp without time zone)
 Total runtime: 14919.169 ms
(7 rows)

hot

                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..1287.98 rows=76 width=57) (actual time=171.584..171.584 rows=0 loops=1)
   -> Nested Loop (cost=0.00..130018.16 rows=7672 width=57) (actual time=171.581..171.581 rows=0 loops=1)
         -> Index Scan using temporaryblobstorage_file_alias_key on temporaryblobstorage (cost=0.00..1016.83 rows=17573 width=53) (actual time=0.024..21.512 rows=17573 loops=1)
         -> Index Scan using libraryfilealias_pkey on libraryfilealias (cost=0.00..7.33 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=17573)
               Index Cond: (libraryfilealias.id = temporaryblobstorage.file_alias)
               Filter: (libraryfilealias.expires > '2011-03-08 04:28:14.106317'::timestamp without time zone)
 Total runtime: 171.663 ms
(7 rows)

Time: 174.048 ms

[Launchpad QA Bot (lpqabot)]

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