Uploaded image for project: 'MusicBrainz Server'
  1. MusicBrainz Server
  2. MBS-9708

Querying area containments is very slow

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2018-05-09
    • Component/s: None
    • Labels:
      None

      Description

      The area_containment view has a major flaw, which is that (at least in PostgreSQL 9.5) the recursive CTE it uses requires calculating containments for all areas, even though we only need them for the areas passed to load_containment (usually just one area). See the definition.

      We have a cache for the containments set up, but it isn't very effective because it must be invalidated often (for all areas).

      The area_containment view was apparently added for SEARCH-359 (see the relevant commit), but it's not used anywhere in the current or future search indexers.

      I propose dropping the area_containment view and inlining the query into load_containment, where we can parameterize it to ensure it only calculates containments for the areas we need.

        Attachments

          Activity

            People

            Assignee:
            bitmap Michael Wiencek
            Reporter:
            bitmap Michael Wiencek
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Packages

                Version Package
                2018-05-09