[wp-trac] [WordPress Trac] #50161: Slow query in WP_Posts_List_Table, no way to avoid with filters/hooks

[WordPress Trac]

#50161: Slow query in WP_Posts_List_Table, no way to avoid with filters/hooks
--------------------------------+------------------------------------------
 Reporter:  LucasMS             |       Owner:  SergeyBiryukov
     Type:  enhancement         |      Status:  accepted
 Priority:  normal              |   Milestone:  6.9
Component:  Posts, Post Types   |     Version:  5.4.1
 Severity:  normal              |  Resolution:
 Keywords:  has-patch 2nd-      |     Focuses:  administration, performance
  opinion                       |
--------------------------------+------------------------------------------

Comment (by jonsurrell):

 I spent a lot of time to understand this and here are my findings:

 - Unsurprisingly, the available indexes seem fine when there are not a lot
 of rows. Queries on tables few rows are not particularly interesting
 because they tend to be fast regardless.
 - When the rows get into the hundreds of thousands, the query slows down
 noticeably. [https://core.trac.wordpress.org/ticket/50161#comment:6
 WordPress should remain performant well beyond this threshold.]
 - I generated data and did a lot of testing. I found at least three
 different compound keys that yield significant performance improvements in
 the neighborhood of 3x faster.

 Based on my findings I'm in favor of adding the new index as proposed in
 [https://github.com/WordPress/wordpress-develop/pull/9312 PR 9312].

 -----

 [https://core.trac.wordpress.org/ticket/50161#comment:12 This is a
 relevant point and one I was wondering about.] Is the new index more
 broadly applicable, or can it be?

 > Actually, the SQL inside of `wp_count_posts()` could probably be
 rewritten to leverage this new index, as it's the same `post_type`,
 `post_status`, `post_author` combo thanks to `readable` being used.

 I tested this out on a smaller test site. I added three different indexes
 (described below), monitored some queries with the Query Monitor plugin
 and then explained them.

 I observed that queries without `readable` continued to use an existing
 index, but _do_ have the type_status_author index as a candidtate:

 {{{
 EXPLAIN SELECT post_status, COUNT( * ) AS num_posts FROM wp_posts WHERE
 post_type = 'post' GROUP BY post_status;
 +------+-------------+----------+------+-------------------------------------+------------------+---------+-------+------+--------------------------+
 | id   | select_type | table    | type | possible_keys
 | key              | key_len | ref   | rows | Extra                    |
 +------+-------------+----------+------+-------------------------------------+------------------+---------+-------+------+--------------------------+
 |    1 | SIMPLE      | wp_posts | ref  |
 type_status_date,type_status_author | type_status_date | 82      | const |
 217  | Using where; Using index |
 +------+-------------+----------+------+-------------------------------------+------------------+---------+-------+------+--------------------------+
 }}}

 However, when I force the readable code path to be entered to adjust the
 query, I observed the following:

 {{{
 EXPLAIN SELECT post_status, COUNT( * ) AS num_posts FROM wp_posts WHERE
 post_type = 'post' AND (post_status != 'private' OR ( post_author = 1 AND
 post_status = 'private' )) GROUP BY post_status;
 +------+-------------+----------+-------+---------------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------+
 | id   | select_type | table    | type  | possible_keys
 | key                | key_len | ref  | rows | Extra                    |
 +------+-------------+----------+-------+---------------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------+
 |    1 | SIMPLE      | wp_posts | range |
 type_status_date,post_author,type_status_author,author_type_status,type_author_status
 | type_status_author | 172     | NULL | 218  | Using where; Using index |
 +------+-------------+----------+-------+---------------------------------------------------------------------------------------+--------------------+---------+------+------+--------------------------+
 }}}

 The proposed `type_author_status` is used! The other indexes I tried were
 also available in this case, but were not selected. This is more supports
 my conclusion below that the proposed index is a good choice.

 -----

 I don't have a site available for benchmarking with sufficiently large
 posts table, but I did some benchmarking locally that align with the
 findings @josephscott
 [https://core.trac.wordpress.org/ticket/50161#comment:7 shared above.]

 I generated a posts table and inserted random data. Users were randomly
 selected from 7 users, post types were random from the standard set of
 post types, and so on for status, etc. The table contained 400,000 rows.

 I tried a few different ways of benchmarking, primarily I used
 [https://github.com/sharkdp/hyperfine hyperfine] to run the query via PHP
 with or without different indexes.

 I tried three different indexes:
 - No new index (current WordPress Core).
 - TSA as proposed by @josephscott `(post_type, post_status, post_author)`
 - ATS `(post_author, post_type, post_status )`
 - TAS `(post_type, post_author, post_status )`

 In my test data, TSA, ATS, and TAS all seemed to yield a greater than 3x
 improvement. ATS and TAS seemed to yield the largest speedup with a
 negligible difference, while TSA had ''slightly'' smaller improvement.

 Given that this was generated test data and not likely to follow real
 world data patterns (for example, I doubt the distribution of post type,
 author, and status is uniform) I'm confident enough that the performance
 improvement is real and likely to mitigate the problem described in this
 ticket for large sites. I'm not confident enough in the results to
 recommend a specific index over another and would instead rely on the TSA
 index that @josephscott selected based on working with real sites.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/50161#comment:31>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform