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

Speed up /ws/js/check_duplicates

XMLWordPrintable

      The postgres logs are frequently spammed with slow queries like:

      LOG:  duration: 2413.117 ms  statement: SELECT 1 FROM artist WHERE lower(musicbrainz_unaccent(name)) = lower(musicbrainz_unaccent('x')) AND lower(musicbrainz_unaccent(comment)) = lower(musicbrainz_unaccent('y')) LIMIT 1
      
      LOG:  duration: 1593.464 ms  statement: WITH search (term) AS (    VALUES ('x')),     entity_matches (term, entity) AS (        SELECT term, artist FROM artist_alias           JOIN search ON lower(musicbrainz_unaccent(artist_alias.name)) = lower(musicbrainz_unaccent(term))        UNION SELECT term, id FROM artist            JOIN search ON lower(musicbrainz_unaccent(artist.name)) = lower(musicbrainz_unaccent(term)))      SELECT term AS search_term, musicbrainz_unaccent(name) AS unaccented_name, artist.id, artist.gid, artist.name, artist.sort_name, artist.type, artist.area, artist.begin_area, artist.end_area, gender, artist.edits_pending, artist.comment, artist.last_updated, artist.begin_date_year, artist.begin_date_month, artist.begin_date_day, artist.end_date_year, artist.end_date_month,
      

      These both come from /ws/js/check_duplicates and are due to the lack of indexes on lower(musicbrainz_unaccent(name)).

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

              Created:
              Updated:
              Resolved:

                Version Package
                2021-03-15
                Schema change, 2021 Q2