SourcePackage:+index timeout

This query:
SELECT SourcePackagePublishingHistory.ancestor,
       SourcePackagePublishingHistory.archive,
       SourcePackagePublishingHistory.component,
       SourcePackagePublishingHistory.datecreated,
       SourcePackagePublishingHistory.datemadepending,
       SourcePackagePublishingHistory.datepublished,
       SourcePackagePublishingHistory.dateremoved,
       SourcePackagePublishingHistory.datesuperseded,
       SourcePackagePublishingHistory.distroseries,
       SourcePackagePublishingHistory.id,
       SourcePackagePublishingHistory.pocket,
       SourcePackagePublishingHistory.removal_comment,
       SourcePackagePublishingHistory.removed_by,
       SourcePackagePublishingHistory.scheduleddeletiondate,
       SourcePackagePublishingHistory.section,
       SourcePackagePublishingHistory.sourcepackagerelease,
       SourcePackagePublishingHistory.status,
       SourcePackagePublishingHistory.supersededby,
       SourcePackageRelease.user_defined_fields,
       SourcePackageRelease.architecturehintlist,
       SourcePackageRelease.build_conflicts,
       SourcePackageRelease.build_conflicts_indep,
       SourcePackageRelease.builddepends,
       SourcePackageRelease.builddependsindep,
       SourcePackageRelease.changelog,
       SourcePackageRelease.changelog_entry,
       SourcePackageRelease.component,
       SourcePackageRelease.copyright,
       SourcePackageRelease.creator,
       SourcePackageRelease.dateuploaded,
       SourcePackageRelease.dsc,
       SourcePackageRelease.dsc_binaries,
       SourcePackageRelease.dsc_format,
       SourcePackageRelease.dsc_maintainer_rfc822,
       SourcePackageRelease.dsc_standards_version,
       SourcePackageRelease.dscsigningkey,
       SourcePackageRelease.format,
       SourcePackageRelease.homepage,
       SourcePackageRelease.id,
       SourcePackageRelease.maintainer,
       SourcePackageRelease.section,
       SourcePackageRelease.sourcepackage_recipe_build,
       SourcePackageRelease.sourcepackagename,
       SourcePackageRelease.upload_archive,
       SourcePackageRelease.upload_distroseries,
       SourcePackageRelease.urgency,
       SourcePackageRelease.VERSION
FROM SourcePackagePublishingHistory,
     SourcePackageRelease
WHERE SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id
  AND SourcePackageRelease.sourcepackagename = 60982
  AND SourcePackagePublishingHistory.distroseries = 103
  AND SourcePackagePublishingHistory.archive IN (1,
                                                 534)
  AND (1=1)
ORDER BY SourcePackageRelease.VERSION, SourcePackagePublishingHistory.datepublished

has this plan cold:

                                                                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort (cost=37068.19..37081.05 rows=5147 width=1919) (actual time=16714.823..16714.847 rows=72 loops=1)
   Sort Key: sour...

May be a fat index on a slave; need to check the plan on all prod servers

The status IN (2) queries are from SourcePackage.published_by_pocket and latest_published_component (used in the JSON representation), the others are SourcePackage.releases (sometimes called directly by the templates, others by SourcePackage.summary).

Analysis has found that this is the copyright field in SPR - its transferring 81MB of data to the appservers in the repeated query.

Diagnosed as the copywrite field making the table too wide. Time is spent decoding huge amounts of data into Python Unicode strings, despite there not being many rows retrieved.

I am disappointed to read this. The copyright info is needed for linking projects and packages. We have two pages that show the content sp+copyright and in product registration from the SP page. I think we want to also have dsp+copyright. Regardless, we rarely *need* the copyright, we just need to show it in these 2-3 cases. Can we do move copyright to another table/object so that we can retrieve it on demand?

On Fri, Mar 11, 2011 at 5:23 AM, Curtis Hovey
<email address hidden> wrote:
> I am disappointed to read this. The copyright info is needed for linking
> projects and packages. We have two pages that show the content
> sp+copyright and in product registration from the SP page. I think we
> want to also have dsp+copyright. Regardless, we rarely *need* the
> copyright, we just need to show it in these 2-3 cases. Can we do move
> copyright to another table/object so that we can retrieve it on demand?

Yes, we can move it to another place. That doesn't need to be in-db
though, unless:
 - we join on it
 - we show it as a column in a report

A librarian file can be shown (e.g. in an iframe) without any
appserver overhead by having the client grab and render it. Or we can
have it in a dedicated table in the db (incidentally de-duplicating it
at the same time) if we need it to be in the DB.