gistfile1.txt

EXPLAIN ANALYZE WITH x AS (SELECT DISTINCT recording.id, recording.gid, recording.name COLLATE musicbrainz,
   recording.artist_credit AS artist_credit_id,
   recording.length, recording.comment, recording.video,
   recording.edits_pending, recording.last_updated,
               recording.name COLLATE musicbrainz AS name_collate,
               comment COLLATE musicbrainz AS comment_collate
        FROM recording
            JOIN artist_credit_name acn
                ON acn.artist_credit = recording.artist_credit
        WHERE acn.artist = '97546'
        ORDER BY recording.name COLLATE musicbrainz,
                 comment COLLATE musicbrainz OFFSET '49900')
   SELECT x.*, c.count AS total_row_count
   FROM x, (SELECT count(*) FROM x) c
LIMIT '100';

pink: Execution Time: 1651.016 ms
jimmy: Execution Time: 1220.915 ms
26.05% faster

---

EXPLAIN ANALYZE WITH release_mbids(id) AS (
    SELECT rel.id
      FROM mapping.canonical_recording_release_redirect crrr
      JOIN musicbrainz.release rel
        ON crrr.release_mbid = rel.gid
      JOIN musicbrainz.release_group rg
        ON rel.release_group = rg.id
      JOIN musicbrainz.release_group_tag rgt
        ON rgt.release_group = rel.release_group
      JOIN musicbrainz.tag t
        ON rgt.tag = t.id
 LEFT JOIN musicbrainz.genre g
        ON t.name = g.name
     WHERE rgt.last_updated > '2023-05-29T12:00:01.693499'::timestamp
        OR   g.last_updated > '2023-05-29T12:00:01.693499'::timestamp
UNION
    SELECT rel.id
      FROM mapping.canonical_recording_release_redirect crrr
      JOIN musicbrainz.release rel
        ON crrr.release_mbid = rel.gid
      JOIN musicbrainz.release_group rg
        ON rel.release_group = rg.id
 LEFT JOIN cover_art_archive.cover_art caa
        ON caa.release = rel.id
 LEFT JOIN cover_art_archive.cover_art_type cat
        ON cat.id = caa.id
     WHERE  rel.last_updated > '2023-05-29T12:00:01.693499'::timestamp
        OR   rg.last_updated > '2023-05-29T12:00:01.693499'::timestamp
        OR (caa.date_uploaded > '2023-05-29T12:00:01.693499'::timestamp AND (type_id = 1 OR type_id IS NULL))
) SELECT r.gid
    FROM musicbrainz.recording r
    JOIN musicbrainz.track t
      ON t.recording = r.id
    JOIN musicbrainz.medium m
      ON m.id = t.medium
    JOIN release_mbids rm
      ON rm.id = m.release;

pink:  Execution Time: 37551.163 ms
jimmy:  Execution Time: 29753.359 ms
20.77% faster

---

EXPLAIN ANALYZE SELECT "release_mbid","release_group_mbid","release_name","album_artist_name","caa_id","caa_release_mbid","artist_credit_mbids" FROM (
      WITH release_group_cover_art AS (
            SELECT DISTINCT ON (rg.id)
                   rg.id AS release_group
                 , caa.id AS caa_id
                 , caa_rel.gid AS caa_release_mbid
              FROM musicbrainz.release_group rg
              JOIN musicbrainz.release caa_rel
                ON rg.id = caa_rel.release_group
         LEFT JOIN (
                  SELECT release, date_year, date_month, date_day
                    FROM musicbrainz.release_country
               UNION ALL
                  SELECT release, date_year, date_month, date_day
                    FROM musicbrainz.release_unknown_country
                 ) re
                ON (re.release = caa_rel.id)
         FULL JOIN cover_art_archive.release_group_cover_art rgca
                ON rgca.release = caa_rel.id
         LEFT JOIN cover_art_archive.cover_art caa
                ON caa.release = caa_rel.id
         LEFT JOIN cover_art_archive.cover_art_type cat
                ON cat.id = caa.id
             WHERE type_id = 1
               AND mime_type != 'application/pdf'
          ORDER BY rg.id
                 , rgca.release
                 , re.date_year
                 , re.date_month
                 , re.date_day
                 , caa.ordering
      ), release_cover_art AS (
            SELECT DISTINCT ON (rel.gid)
                   rel.gid AS release_mbid
                 , caa.id AS caa_id
                 , rel.gid AS caa_release_mbid
              FROM musicbrainz.release rel
              JOIN cover_art_archive.cover_art caa
                ON caa.release = rel.id
              JOIN cover_art_archive.cover_art_type cat
                ON cat.id = caa.id
             WHERE type_id = 1
               AND mime_type != 'application/pdf'
          ORDER BY rel.gid
                 , caa.ordering
      ), intermediate AS (
            SELECT rel.gid AS release_mbid
                 , rg.gid AS release_group_mbid
                 , rel.name AS release_name
                 , ac.name AS album_artist_name
                 , COALESCE(rac.caa_id, rgac.caa_id) AS caa_id
                 , COALESCE(rac.caa_release_mbid, rgac.caa_release_mbid) AS caa_release_mbid
                 , a.gid AS artist_mbid
                 , acn.position
              FROM musicbrainz.release rel
              JOIN musicbrainz.release_group rg
                ON rel.release_group = rg.id
              JOIN musicbrainz.artist_credit ac
                ON rel.artist_credit = ac.id
              JOIN musicbrainz.artist_credit_name acn
                ON acn.artist_credit = ac.id
              JOIN musicbrainz.artist a
                ON acn.artist = a.id
         LEFT JOIN release_cover_art rac
                ON rac.release_mbid = rel.gid
         LEFT JOIN release_group_cover_art rgac
                ON rgac.release_group = rel.release_group
      )
            SELECT release_mbid
                 , release_group_mbid
                 , release_name
                 , album_artist_name
                 , caa_id
                 , caa_release_mbid
                 , array_agg(artist_mbid ORDER BY position) AS artist_credit_mbids
              FROM intermediate
          GROUP BY release_mbid
                 , release_group_mbid
                 , release_name
                 , album_artist_name
                 , caa_id
                 , caa_release_mbid
) SPARK_GEN_SUBQ_184;

pink: Execution Time: 41641.645 ms
jimmy: Execution Time: 32637.252 ms
21.62% faster