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

Add a materialized area_containment table kept up-to-date with triggers

XMLWordPrintable

      We used to have an area_containment view to calculate parent -> descendant connections between all areas (via "part of" relationships).  This was removed by MBS-9708, because in-lining the query with a hard-coded parent ID was determined to be much faster than the view.

      Unfortunately queries that use containment information (like the listing of all artists from an area) are still quite slow and frequently appear in our production postgres instance's slow query logs.  I propose we bring back area_containment as a materialized table which is kept up-to-date via triggers.  The triggers would execute on changes to area-area "part of" relationships.  We can also add indices on this table to make queries by parent or descendant very fast.

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

              Created:
              Updated:

                Version Package
                Schema change, 2022 Q2