Page MenuHomePhabricator

SELECT /* Title::getFirstRevision */ sometimes using page_user_timestamp index instead of page_timestamp
Closed, ResolvedPublic

Description

While working on T223151 we realised that the optimizer is choosing the wrong plan in certain hosts for the following query (T223151#5599272):

SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` IGNORE INDEX (rev_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;

This is the index chose across enwiki:

dbstore1003.eqiad.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	24848	Using where; Using filesort
db2130.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25896	Using where
db2116.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25896	Using where
db2112.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	23624	Using where; Using filesort
db2103.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	21700	Using where; Using filesort
db2102.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	27758	Using where; Using filesort
db2097.codfw.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25896	Using where
db2094.codfw.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25550	Using where
db2092.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	25350	Using where; Using filesort
db2088.codfw.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,rev_page_id,page_user_timestamp	page_timestamp	4	const	5124	Using where
db2085.codfw.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,rev_page_id,page_user_timestamp	page_timestamp	4	const	5025	Using where
db2072.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25896	Using where
db2071.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	25350	Using where; Using filesort
db2048.codfw.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	23924	Using where; Using filesort
db1139.eqiad.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_user_timestamp	4	const	25614	Using where; Using filesort
db1134.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25896	Using where
db1124.eqiad.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25550	Using where
db1119.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_timestamp	4	const	27054	Using where
db1118.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_user_timestamp	4	const	24504	Using where; Using filesort
db1114.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_timestamp	4	const	24974	Using where
db1106.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_timestamp	4	const	27216	Using where
db1105.eqiad.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	5214	Using where
db1099.eqiad.wmnet:3311
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	5098	Using where
db1089.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_user_timestamp	4	const	25706	Using where; Using filesort
db1083.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	25896	Using where
db1080.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,page_timestamp,page_user_timestamp,rev_page_id	page_timestamp	4	const	24130	Using where
db1067.eqiad.wmnet:3306
1	SIMPLE	revision	ref	PRIMARY,rev_page_id,page_timestamp,page_user_timestamp	page_timestamp	4	const	25896	Using where

Per @Anomie's comment at T223151#5600630:

There's no reason for db1089 not to use the other plan, the rev_user column included in the page_user_timestamp index isn't referenced anywhere. Seems like one of those cases where it just chooses a wrong plan for no obvious reason. If it becomes a problem we could always ignore the index or force the right one.

This is the difference on db1099:3311 and db1089:

db1099:3311

root@db1099.eqiad.wmnet[enwiki]> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` IGNORE INDEX (rev_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+
| id   | select_type | table               | type   | possible_keys                                          | key            | key_len | ref                                           | rows | Extra       |
+------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+
|    1 | SIMPLE      | revision            | ref    | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_timestamp | 4       | const                                         | 5100 | Using where |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp                   | PRIMARY        | 4       | enwiki.revision.rev_id                        |    1 | Using index |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                                                | PRIMARY        | 8       | enwiki.temp_rev_user.revactor_actor           |    1 |             |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                 | PRIMARY        | 4       | enwiki.revision.rev_id                        |    1 | Using index |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                | PRIMARY        | 8       | enwiki.temp_rev_comment.revcomment_comment_id |    1 |             |
+------+-------------+---------------------+--------+--------------------------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+
5 rows in set (0.00 sec)
db1089 (non partitioned):

root@db1089.eqiad.wmnet[enwiki]> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` IGNORE INDEX (rev_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
| id   | select_type | table               | type   | possible_keys                                          | key                 | key_len | ref                                           | rows  | Extra                       |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
|    1 | SIMPLE      | revision            | ref    | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_user_timestamp | 4       | const                                         | 25706 | Using where; Using filesort |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                 | PRIMARY             | 4       | enwiki.revision.rev_id                        |     1 | Using index                 |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                | PRIMARY             | 8       | enwiki.temp_rev_comment.revcomment_comment_id |     1 |                             |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp                   | PRIMARY             | 4       | enwiki.revision.rev_id                        |     1 | Using index                 |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                                                | PRIMARY             | 8       | enwiki.temp_rev_user.revactor_actor           |     1 |                             |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
5 rows in set (0.00 sec)

The query still runs fast with the wrong index 0.16 vs 0.01, but it is something to keep in mind.
An analyze table revision on db2092 didn't change the query plan:

root@db2092.codfw.wmnet[enwiki]> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` IGNORE INDEX (rev_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
| id   | select_type | table               | type   | possible_keys                                          | key                 | key_len | ref                                           | rows  | Extra                       |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
|    1 | SIMPLE      | revision            | ref    | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | page_user_timestamp | 4       | const                                         | 25350 | Using where; Using filesort |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp                   | PRIMARY             | 4       | enwiki.revision.rev_id                        |     1 | Using index                 |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                                                | PRIMARY             | 8       | enwiki.temp_rev_user.revactor_actor           |     1 |                             |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                 | PRIMARY             | 4       | enwiki.revision.rev_id                        |     1 | Using index                 |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                | PRIMARY             | 8       | enwiki.temp_rev_comment.revcomment_comment_id |     1 |                             |
+------+-------------+---------------------+--------+--------------------------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
5 rows in set (0.04 sec)

We can try to rebuild the table entirely to see if that helps.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 24 2019, 12:24 PM
Marostegui triaged this task as Medium priority.Oct 24 2019, 12:24 PM
Marostegui moved this task from Triage to Next on the DBA board.

Looks like the statistics are probably ok-ish, forcing the index each way shows similar row estimates on both hosts.

db1089
wikiadmin@10.64.32.115(enwiki)> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` FORCE INDEX (page_user_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
| id   | select_type | table               | type   | possible_keys                        | key                 | key_len | ref                                           | rows  | Extra                       |
+------+-------------+---------------------+--------+--------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+
|    1 | SIMPLE      | revision            | ref    | page_user_timestamp                  | page_user_timestamp | 4       | const                                         | 25706 | Using where; Using filesort |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev               | PRIMARY             | 4       | enwiki.revision.rev_id                        |     1 | Using index                 |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                              | PRIMARY             | 8       | enwiki.temp_rev_comment.revcomment_comment_id |     1 |                             |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp | PRIMARY             | 4       | enwiki.revision.rev_id                        |     1 | Using index                 |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                              | PRIMARY             | 8       | enwiki.temp_rev_user.revactor_actor           |     1 |                             |
+------+-------------+---------------------+--------+--------------------------------------+---------------------+---------+-----------------------------------------------+-------+-----------------------------+

wikiadmin@10.64.32.115(enwiki)> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` FORCE INDEX (page_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+
| id   | select_type | table               | type   | possible_keys                        | key            | key_len | ref                                           | rows  | Extra       |
+------+-------------+---------------------+--------+--------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+
|    1 | SIMPLE      | revision            | ref    | page_timestamp                       | page_timestamp | 4       | const                                         | 25896 | Using where |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev               | PRIMARY        | 4       | enwiki.revision.rev_id                        |     1 | Using index |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                              | PRIMARY        | 8       | enwiki.temp_rev_comment.revcomment_comment_id |     1 |             |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp | PRIMARY        | 4       | enwiki.revision.rev_id                        |     1 | Using index |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                              | PRIMARY        | 8       | enwiki.temp_rev_user.revactor_actor           |     1 |             |
+------+-------------+---------------------+--------+--------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+
db1099:3311
wikiadmin@10.64.16.84(enwiki)> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` FORCE INDEX (page_user_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------+---------------------+---------+-----------------------------------------------+------+-----------------------------+
| id   | select_type | table               | type   | possible_keys                        | key                 | key_len | ref                                           | rows | Extra                       |
+------+-------------+---------------------+--------+--------------------------------------+---------------------+---------+-----------------------------------------------+------+-----------------------------+
|    1 | SIMPLE      | revision            | ref    | page_user_timestamp                  | page_user_timestamp | 4       | const                                         | 5097 | Using where; Using filesort |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp | PRIMARY             | 4       | enwiki.revision.rev_id                        |    1 | Using index                 |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                              | PRIMARY             | 8       | enwiki.temp_rev_user.revactor_actor           |    1 |                             |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev               | PRIMARY             | 4       | enwiki.revision.rev_id                        |    1 | Using index                 |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                              | PRIMARY             | 8       | enwiki.temp_rev_comment.revcomment_comment_id |    1 |                             |
+------+-------------+---------------------+--------+--------------------------------------+---------------------+---------+-----------------------------------------------+------+-----------------------------+

wikiadmin@10.64.16.84(enwiki)> explain SELECT /* Title::getFirstRevision xx */  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`  FROM `revision` FORCE INDEX (page_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))   WHERE rev_page = '18762503'  ORDER BY rev_timestamp ASC, rev_id ASC LIMIT 1;
+------+-------------+---------------------+--------+--------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+
| id   | select_type | table               | type   | possible_keys                        | key            | key_len | ref                                           | rows | Extra       |
+------+-------------+---------------------+--------+--------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+
|    1 | SIMPLE      | revision            | ref    | page_timestamp                       | page_timestamp | 4       | const                                         | 5097 | Using where |
|    1 | SIMPLE      | temp_rev_user       | ref    | PRIMARY,revactor_rev,actor_timestamp | PRIMARY        | 4       | enwiki.revision.rev_id                        |    1 | Using index |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                              | PRIMARY        | 8       | enwiki.temp_rev_user.revactor_actor           |    1 |             |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev               | PRIMARY        | 4       | enwiki.revision.rev_id                        |    1 | Using index |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                              | PRIMARY        | 8       | enwiki.temp_rev_comment.revcomment_comment_id |    1 |             |
+------+-------------+---------------------+--------+--------------------------------------+----------------+---------+-----------------------------------------------+------+-------------+

It seems the planner is just not taking into account that the good plan will be able to stop early while the filesort will have to run to completion (plus have to actually sort). We've seen that before. I forget whether later versions improved that or not.

I note that in the hopefully-not-too-distant future we'll be dropping the page_user_timestamp index (T161671). Although then we might have the same problem with the replacement page_actor_timestamp index.

For what is worth, db1107 (10.4) was recloned from db1089....and db1107 shows the correct plan (uses page_timestamp) whereas db1089 shows the wrong plan (page_user_timestamp).
So maybe the optimizer is smarter on 10.4 for this particular query (or could be just coincidence) on this particular host. We'll see once we've migrated entirely.

daniel added a subscriber: daniel.Apr 21 2020, 7:40 PM

For what is worth, db1107 (10.4) was recloned from db1089....and db1107 shows the correct plan (uses page_timestamp) whereas db1089 shows the wrong plan (page_user_timestamp).
So maybe the optimizer is smarter on 10.4 for this particular query (or could be just coincidence) on this particular host. We'll see once we've migrated entirely.

@Marostegui is this still an issue?

It is still an issue - once we've migrated more hosts to 10.4 we can evaluate whether this is entirely fixed on 10.4 or not.

It is still an issue - once we've migrated more hosts to 10.4 we can evaluate whether this is entirely fixed on 10.4 or not.

Do you think we should use FORCE INDEX for now? Or wait and see?

Let's wait for now I would say - I will try to upgrade a couple of more hosts on enwiki this week

Marostegui closed this task as Resolved.Apr 29 2020, 7:48 AM
Marostegui claimed this task.

All the hosts are now using page_timestamp (either 10.1.43 and 10.4) so closing this.

Aklapper removed a subscriber: Anomie.Fri, Oct 16, 5:41 PM