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

Add materialized tables to fetch release groups by artist or track artist

    XMLWordPrintable

    Details

      Description

      Artist pages that have a ton of release groups are slow to load, because the queries to find those release groups are slow. These queries frequently spam our PG logs. The primary reason for this, besides the number of joins involved, is the actual sorting of all the results. In conjunction with pagination, PG has to read and sort all results for every page, on every page. The sort order is also fairly complex, being based on a number of joined tables and aggregate columns, not to mention any filters (status, artist credit, etc.) applied.

      Ideally, we would avoid having PG sort these kinds of queries at all costs by returning things in index order. The proposal here is thus to add a materialized table associating artists (and a separate table associating track artists) to release groups, along with all the necessary sort columns. A B-tree index can then be created on these tables to establish a correct, fixed order when scanning results.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:

                  Packages

                  Version Package
                  Schema change, 2021 Q2