Page MenuHomePhabricator

create revision_commentindex
Open, Needs TriagePublic

Description

While trying to query revision table based on comments in toolforge environment, I noticed awful performance for simple query like:
select rev_id, comment_text from revision inner join comment on rev_comment_id=comment_id where comment_text like '%CSD#G11%' limit 10;

Following the good work in T166733 and related tasks, I think it may be cool to have revision_commentindex similar to revision_userindex in toolsforge for use by tools that query based on comments.

Event Timeline

eranroz created this task.Nov 4 2018, 8:52 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 4 2018, 8:52 PM
Krenair added a subscriber: Krenair.Nov 4 2018, 9:14 PM
JJMC89 edited projects, added Data-Services; removed Cloud-Services.Nov 4 2018, 9:50 PM
JJMC89 moved this task from Backlog to Wiki replicas on the Data-Services board.

I note there are likely three sources of slowness here:

  1. Scanning comment for comment_text like '%CSD#G11%' can't use any indexes.
  2. Accessing comment in general is slow thanks to having to do many subqueries per row to check visibility.
  3. The query can't make use of the revcomment_comment_id index, because the revision view has that field aliased.

This request would help with #3. #2 is being discussed as T215445: comment and actor view challenges for Cloud Services. I doubt there's anything we can do about #1, that part will always be slow.