Launchpad itself

Product:+index timeouts

Bug #739051 reported by Robert Collins on 2011-03-20

This bug affects 3 people

Affects		Status	Importance	Assigned to	Milestone
	Launchpad itself	Fix Released	Critical	William Grant

Bug Description

  6 SELECT DISTINCT DistributionSourcePackageCache.archive, DistributionSourcePackageCache.binpkgdesc ... istroseries = $INT ORDER BY rank DESC, DistributionSourcePackageCache.name LIMIT $INT OFFSET $INT:
   GET: 6 Robots: 0 Local: 0
      3 https://launchpad.net/ubuntu-it/+index (Product:+index)
       OOPS-1926E737, OOPS-1926G741, OOPS-1926G742
      1 https://launchpad.net/glib/+index (Product:+index)
       OOPS-1926H1059
      1 https://launchpad.net/indicator-applet/+index (Product:+index)
       OOPS-1926B1590
   [1 other URLs]

  6 SELECT ProductReleaseFile.date_uploaded, ProductReleaseFile.description, ProductReleaseFile.filet ... .productrelease IN ($INT ... $INT) AND ($INT=$INT) ORDER BY ProductReleaseFile.date_uploaded DESC:
   GET: 6 Robots: 0 Local: 6
      6 https://launchpad.net/rhythmbox/+index (Product:+index)
       OOPS-1926C1169, OOPS-1926E1448, OOPS-1926G1357, OOPS-1926H1436, OOPS-1926H1439

slow SQL looking guessing at packaging
11706ms SQL-launchpad-main-slave SELECT DISTINCT DistributionSourcePackageCache.archive, DistributionSourcePackageCache.binpkgdescriptions, DistributionSourcePackageCache.binpkgnames, DistributionSourcePackageCache.binpkgsummaries, DistributionSourcePackageCache.changelog, DistributionSourcePackageCache.distribution, DistributionSourcePackageCache.id, DistributionSourcePackageCache.name, DistributionSourcePackageCache.sourcepackagename, SourcePackageName.id, SourcePackageName.name, rank(fti, ftq('indicator-applet')) AS rank
FROM DistributionSourcePackageCache
JOIN SourcePackageName ON DistributionSourcePackageCache.sourcepackagename = SourcePackageName.id
JOIN SourcePackageRelease ON SourcePackageRelease.sourcepackagename = SourcePackageName.id
JOIN SourcePackagePublishingHistory ON SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id
WHERE DistributionSourcePackageCache.distribution = 1
  AND DistributionSourcePackageCache.archive IN (1,
                                                 534)
  AND (DistributionSourcePackageCache.fti @@ ftq('indicator-applet')
       OR DistributionSourcePackageCache.name ILIKE '%' || 'indicator-applet' || '%')
  AND NOT EXISTS
    (SELECT 1
     FROM Packaging
     WHERE Packaging.sourcepackagename = SourcePackageName.id)
  AND SourcePackagePublishingHistory.distroseries = 106
ORDER BY rank DESC, DistributionSourcePackageCache.name LIMIT 8
OFFSET 0

See original description

Tags:

Related branches

lp:~wgrant/launchpad/dspc-trgm-indices

Merged into lp:launchpad at revision 15584

Stuart Bishop (community): Approve (db) on 2012-07-09

lp:~wgrant/launchpad/package-search-timeouts

Merged into lp:launchpad at revision 15588

Curtis Hovey (community): Approve (code) on 2012-06-13

Robert Collins (lifeless) on 2011-04-11

description:	updated
description:	updated

Revision history for this message

Robert Collins (lifeless) wrote on 2011-04-11:

select count(*) from DistributionSourcePackageCache;
count
-------
96686

Revision history for this message

Robert Collins (lifeless) wrote on 2011-04-11:

Download full text (7.8 KiB)

cold
Limit (cost=17832.17..17832.43 rows=8 width=1255) (actual time=4598.463..4599.105 rows=3 loops=1)
   -> Unique (cost=17832.17..17836.39 rows=130 width=1255) (actual time=4598.461..4599.099 rows=3 loops=1)
         -> Sort (cost=17832.17..17832.49 rows=130 width=1255) (actual time=4598.458..4598.462 rows=21 loops=1)
               Sort Key: (rank((distributionsourcepackagecache.fti)::tsvector, '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery)), distributionsourcepackagecache.name, distributionsourcepackagecache.archive, distributionsourcepackagecache.binpkgdescriptions, distributionsourcepackagecache.binpkgnames, distributionsourcepackagecache.binpkgsummaries, distributionsourcepackagecache.changelog, distributionsourcepackagecache.id, distributionsourcepackagecache.sourcepackagename, sourcepackagename.name
               Sort Method: quicksort Memory: 62kB
               -> Nested Loop (cost=345.42..17827.61 rows=130 width=1255) (actual time=978.954..4597.543 rows=21 loops=1)
                     -> Nested Loop (cost=345.42..17448.86 rows=749 width=1259) (actual time=351.189..4244.409 rows=244 loops=1)
                           -> Nested Loop Anti Join (cost=345.42..16827.43 rows=9 width=1255) (actual time=345.667..4207.215 rows=7 loops=1)
                                 -> Nested Loop (cost=345.42..16826.52 rows=10 width=1255) (actual time=30.426..4120.365 rows=28 loops=1)
                                       -> Bitmap Heap Scan on distributionsourcepackagecache (cost=345.42..16763.69 rows=10 width=1237) (actual time=25.031..4036.066 rows=28 loops=1)
                                             Recheck Cond: (archive = ANY ('{1,534}'::integer[]))
                                             Filter: ((distribution = 1) AND (((fti)::tsvector @@ '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery) OR (name ~~* '%indicator-applet%'::text)))
                                             -> Bitmap Index Scan on distributionsourcepackagecache__archive__idx (cost=0.00..345.42 rows=22789 width=0) (actual time=18.823..18.823 rows=22798 loops=1)
                                                   Index Cond: (archive = ANY ('{1,534}'::integer[]))
                                       -> Index Scan using sourcepackagename_pkey on sourcepackagename (cost=0.00..6.27 rows=1 width=18) (actual time=2.999..3.001 rows=1 loops=28)
                                             Index Cond: (sourcepackagename.id = distributionsourcepackagecache.sourcepackagename)
                                 -> Index Scan using packaging_sourcepackagename_idx on packaging (cost=0.00..0.34 rows=4 width=4) (actual time=3.096..3.096 rows=1 loops=28)
                                       Index Cond: (packaging.sourcepackagename = sourcepackagename.id)
                           -> Index Scan using sourcepackagerelease_sourcepackagename_idx on sourcepackagerelease (cost=0.00..68.77 rows=22 width=8) (actual time=4.060..5.273 rows=35 loops=7)
                                 Index Cond: (sourcepackagerelease.sourcepackagename = distributionsourcepackagecache.sourcepackagename)
                     -> Index Scan using securesourcepackagepu...

cold
 Limit  (cost=17832.17..17832.43 rows=8 width=1255) (actual time=4598.463..4599.105 rows=3 loops=1)
   ->  Unique  (cost=17832.17..17836.39 rows=130 width=1255) (actual time=4598.461..4599.099 rows=3 loops=1)
         ->  Sort  (cost=17832.17..17832.49 rows=130 width=1255) (actual time=4598.458..4598.462 rows=21 loops=1)
               Sort Key: (rank((distributionsourcepackagecache.fti)::tsvector, '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery)), distributionsourcepackagecache.name, distributionsourcepackagecache.archive, distributionsourcepackagecache.binpkgdescriptions, distributionsourcepackagecache.binpkgnames, distributionsourcepackagecache.binpkgsummaries, distributionsourcepackagecache.changelog, distributionsourcepackagecache.id, distributionsourcepackagecache.sourcepackagename, sourcepackagename.name
               Sort Method:  quicksort  Memory: 62kB
               ->  Nested Loop  (cost=345.42..17827.61 rows=130 width=1255) (actual time=978.954..4597.543 rows=21 loops=1)
                     ->  Nested Loop  (cost=345.42..17448.86 rows=749 width=1259) (actual time=351.189..4244.409 rows=244 loops=1)
                           ->  Nested Loop Anti Join  (cost=345.42..16827.43 rows=9 width=1255) (actual time=345.667..4207.215 rows=7 loops=1)
                                 ->  Nested Loop  (cost=345.42..16826.52 rows=10 width=1255) (actual time=30.426..4120.365 rows=28 loops=1)
                                       ->  Bitmap Heap Scan on distributionsourcepackagecache  (cost=345.42..16763.69 rows=10 width=1237) (actual time=25.031..4036.066 rows=28 loops=1)
                                             Recheck Cond: (archive = ANY ('{1,534}'::integer[]))
                                             Filter: ((distribution = 1) AND (((fti)::tsvector @@ '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery) OR (name ~~* '%indicator-applet%'::text)))
                                             ->  Bitmap Index Scan on distributionsourcepackagecache__archive__idx  (cost=0.00..345.42 rows=22789 width=0) (actual time=18.823..18.823 rows=22798 loops=1)
                                                   Index Cond: (archive = ANY ('{1,534}'::integer[]))
                                       ->  Index Scan using sourcepackagename_pkey on sourcepackagename  (cost=0.00..6.27 rows=1 width=18) (actual time=2.999..3.001 rows=1 loops=28)
                                             Index Cond: (sourcepackagename.id = distributionsourcepackagecache.sourcepackagename)
                                 ->  Index Scan using packaging_sourcepackagename_idx on packaging  (cost=0.00..0.34 rows=4 width=4) (actual time=3.096..3.096 rows=1 loops=28)
                                       Index Cond: (packaging.sourcepackagename = sourcepackagename.id)
                           ->  Index Scan using sourcepackagerelease_sourcepackagename_idx on sourcepackagerelease  (cost=0.00..68.77 rows=22 width=8) (actual time=4.060..5.273 rows=35 loops=7)
                                 Index Cond: (sourcepackagerelease.sourcepackagename = distributionsourcepackagecache.sourcepackagename)
                     ->  Index Scan using securesourcepackagepublishinghistory_sourcepackagerelease_idx on sourcepackagepublishinghistory  (cost=0.00..0.49 rows=1 width=4) (actual time=1.372..1.442 rows=0 loops=244)
                           Index Cond: (sourcepackagepublishinghistory.sourcepackagerelease = sourcepackagerelease.id)
                           Filter: (sourcepackagepublishinghistory.distroseries = 106)
 Total runtime: 4599.373 ms

hot

QUERY PLAN                                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17832.17..17832.43 rows=8 width=1255) (actual time=297.599..297.935 rows=3 loops=1)
   ->  Unique  (cost=17832.17..17836.39 rows=130 width=1255) (actual time=297.597..297.931 rows=3 loops=1)
         ->  Sort  (cost=17832.17..17832.49 rows=130 width=1255) (actual time=297.595..297.608 rows=21 loops=1)
               Sort Key: (rank((distributionsourcepackagecache.fti)::tsvector, '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery)), distributionsourcepackagecache.name, distributionsourcepackagecache.archive, distributionsourcepackagecache.binpkgdescriptions, distributionsourcepackagecache.binpkgnames, distributionsourcepackagecache.binpkgsummaries, distributionsourcepackagecache.changelog, distributionsourcepackagecache.id, distributionsourcepackagecache.sourcepackagename, sourcepackagename.name
               Sort Method:  quicksort  Memory: 62kB
               ->  Nested Loop  (cost=345.42..17827.61 rows=130 width=1255) (actual time=162.578..297.067 rows=21 loops=1)
                     ->  Nested Loop  (cost=345.42..17448.86 rows=749 width=1259) (actual time=76.515..294.706 rows=244 loops=1)
                           ->  Nested Loop Anti Join  (cost=345.42..16827.43 rows=9 width=1255) (actual time=76.493..294.102 rows=7 loops=1)
                                 ->  Nested Loop  (cost=345.42..16826.52 rows=10 width=1255) (actual time=16.526..293.830 rows=28 loops=1)
                                       ->  Bitmap Heap Scan on distributionsourcepackagecache  (cost=345.42..16763.69 rows=10 width=1237) (actual time=16.507..293.461 rows=28 loops=1)
                                             Recheck Cond: (archive = ANY ('{1,534}'::integer[]))
                                             Filter: ((distribution = 1) AND (((fti)::tsvector @@ '''indic'' & ''applet'' | ''indicatorapplet'''::tsquery) OR (name ~~* '%indicator-applet%'::text)))
                                             ->  Bitmap Index Scan on distributionsourcepackagecache__archive__idx  (cost=0.00..345.42 rows=22789 width=0) (actual time=11.016..11.016 rows=22798 loops=1)
                                                   Index Cond: (archive = ANY ('{1,534}'::integer[]))
                                       ->  Index Scan using sourcepackagename_pkey on sourcepackagename  (cost=0.00..6.27 rows=1 width=18) (actual time=0.008..0.009 rows=1 loops=28)
                                             Index Cond: (sourcepackagename.id = distributionsourcepackagecache.sourcepackagename)
                                 ->  Index Scan using packaging_sourcepackagename_idx on packaging  (cost=0.00..0.34 rows=4 width=4) (actual time=0.007..0.007 rows=1 loops=28)
                                       Index Cond: (packaging.sourcepackagename = sourcepackagename.id)
                           ->  Index Scan using sourcepackagerelease_sourcepackagename_idx on sourcepackagerelease  (cost=0.00..68.77 rows=22 width=8) (actual time=0.013..0.067 rows=35 loops=7)
                                 Index Cond: (sourcepackagerelease.sourcepackagename = distributionsourcepackagecache.sourcepackagename)
                     ->  Index Scan using securesourcepackagepublishinghistory_sourcepackagerelease_idx on sourcepackagepublishinghistory  (cost=0.00..0.49 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=244)
                           Index Cond: (sourcepackagepublishinghistory.sourcepackagerelease = sourcepackagerelease.id)
                           Filter: (sourcepackagepublishinghistory.distroseries = 106)
 Total runtime: 298.169 ms

Revision history for this message

Robert Collins (lifeless) wrote on 2011-04-11:

Ok, under 1 second on all three prod servers, so either a transient plan, or ???

Robert Collins (lifeless) on 2011-04-11

description:

updated

Revision history for this message

William Grant (wgrant) wrote on 2011-04-27:

OOPS-1943CF153 is consistent and terribly slow.

Revision history for this message

Bryce Harrington (bryce) wrote on 2011-11-19:

OOPS-bf8bf0c832e190ecda83f623c89e18ac

Was trying to fill in a description for a project I'd just created. The change went through okay, it just failed to display the edited page, so I had to reload it.

Revision history for this message

Robert Collins (lifeless) wrote on 2012-03-02:

Still happening: OOPS-4171dc2f1000578a15350505a835af7f

Revision history for this message

William Grant (wgrant) wrote on 2012-03-07:

If you can convince the page to render even once, you can now work around the issue semi-permanently for that project by selecting any of the packaging options (including the unpackaged option) at the bottom left of the page.

Revision history for this message

William Grant (wgrant) wrote on 2012-05-13:

This becomes easy to fix once we upgrade to PostgreSQL 9.1. We can use GIN for DSPC.fti and a GiST trigram index for DSPC.name, and eliminate the join against SPR using SPPH.sourcepackagename. This makes it ~60ms for most things on DF.

William Grant (wgrant) on 2012-06-13

Changed in launchpad:
assignee:	nobody → William Grant (wgrant)
status:	Triaged → In Progress

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-07-09:

r15584 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/15584) is part of this bug's fix.

tags:

added: qa-needstesting

William Grant (wgrant) on 2012-07-09

tags:

added: qa-ok
removed: qa-needstesting

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2012-07-10:

#10

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

tags:	added: qa-needstesting removed: qa-ok
Changed in launchpad:
status:	In Progress → Fix Committed

William Grant (wgrant) on 2012-07-10

tags:

added: qa-ok
removed: qa-needstesting

William Grant (wgrant) on 2012-07-11

Changed in launchpad:
status:	Fix Committed → Fix Released

Curtis Hovey (sinzui) on 2012-07-12

tags:

added: disclosure infrastructure

Report a bug

This report contains Public information

Everyone can see this information.

Duplicates of this bug

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.