[mb-bugs] [jira] Updated: (MBS-1243) Add indexes on all edit_* tables on the entity column

[Oliver Charles (JIRA)]

     [ http://jira.musicbrainz.org/browse/MBS-1243?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Oliver Charles updated MBS-1243:
--------------------------------

    Fix Version/s: NGS - Release Candidate 2

> Add indexes on all edit_* tables on the entity column
> -----------------------------------------------------
>
>                 Key: MBS-1243
>                 URL: http://jira.musicbrainz.org/browse/MBS-1243
>             Project: MusicBrainz Server
>          Issue Type: Improvement
>    Affects Versions: NGS - Release Candidate 1
>            Reporter: Oliver Charles
>            Assignee: Oliver Charles
>             Fix For: NGS - Release Candidate 2
>
>
> 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)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.musicbrainz.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira