-
Improvement
-
Resolution: Fixed
-
Normal
-
NGS - Release Candidate 1
-
None
-
None
We only have the PK index (on <edit,entity>) but we most often access this via the entity.
See:
musicbrainz_db_raw_rc1=> explain analyze SELECT id, editor, open_time, expire_time, close_time, data, language, type,
musicbrainz_db_raw_rc1-> yes_votes, no_votes, autoedit, status, quality FROM edit WHERE (id IN (SELECT edit FROM edit_artist WHERE artist = '54')) ORDER BY id DESC OFFSET '0';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=98882.61..98882.79 rows=73 width=378) (actual time=633.292..637.175 rows=1386 loops=1)
-> Sort (cost=98882.61..98882.79 rows=73 width=378) (actual time=633.289..634.714 rows=1386 loops=1)
Sort Key: edit.id
Sort Method: quicksort Memory: 1696kB
-> Nested Loop (cost=98691.29..98880.35 rows=73 width=378) (actual time=611.256..629.346 rows=1386 loops=1)
-> HashAggregate (cost=98691.29..98691.49 rows=20 width=4) (actual time=611.207..612.774 rows=1386 loops=1)
-> Seq Scan on edit_artist (cost=0.00..98691.11 rows=73 width=4) (actual time=2.143..609.145 rows=1386 loops=1)
Filter: (artist = 54)
-> Index Scan using edit_pkey on edit (cost=0.00..9.43 rows=1 width=378) (actual time=0.007..0.008 rows=1 loops=1386)
Index Cond: (edit.id = edit_artist.edit)
Total runtime: 638.851 ms
(11 rows)
musicbrainz_db_raw_rc1=> \d edit_artist
Table "musicbrainz.edit_artist"
Column | Type | Modifiers
------------------------
edit | integer | not null
artist | integer | not null
Indexes:
"edit_artist_pkey" PRIMARY KEY, btree (edit, artist)
musicbrainz_db_raw_rc1=> create index edit_artist_artist_idx on edit_artist (artist);
CREATE INDEX
musicbrainz_db_raw_rc1=> explain analyze SELECT id, editor, open_time, expire_time, close_time, data, language, type,
yes_votes, no_votes, autoedit, status, quality FROM edit WHERE (id IN (SELECT edit FROM edit_artist WHERE artist = '54')) ORDER BY id DESC OFFSET '0';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=440.60..440.78 rows=73 width=378) (actual time=28.535..32.403 rows=1386 loops=1)
-> Sort (cost=440.60..440.78 rows=73 width=378) (actual time=28.533..29.923 rows=1386 loops=1)
Sort Key: edit.id
Sort Method: quicksort Memory: 1696kB
-> Nested Loop (cost=249.29..438.34 rows=73 width=378) (actual time=9.151..25.297 rows=1386 loops=1)
-> HashAggregate (cost=249.29..249.49 rows=20 width=4) (actual time=9.123..10.686 rows=1386 loops=1)
-> Index Scan using edit_artist_artist_idx on edit_artist (cost=0.00..249.11 rows=73 width=4) (actual time=0.122..7.102 rows=1386 loops=1)
Index Cond: (artist = 54)
-> Index Scan using edit_pkey on edit (cost=0.00..9.43 rows=1 width=378) (actual time=0.005..0.006 rows=1 loops=1386)
Index Cond: (edit.id = edit_artist.edit)
Total runtime: 33.701 ms
(11 rows)