API: Public timelines extremely slow because of DB query

[AlfredSK]

• [X ] I have searched open and closed issues for duplicates

Bug Description

I have the problem that the public timelines don't show any content in Mastodon compatible apps. When I try to refresh them I get a general network error - the client is running into a timeout.

There is nothing in my server log. The Apache access log is not showing anything no error message and also no success response.

So I tried the same thing with a desktop browser to get meaningful messages or insights via the browser's console. I found out that e.g. https://libranet.de/api/v1/timelines/home is loading a JSON document in less than 800 ms. But when I try to load https://libranet.de/api/v1/timelines/public it takes more than 125000 ms (a.k.a. 2 minutes) to load the JSON document. And that's the reason why the public timelines don't work with e.g. Tusky or Husky.

The same public timeline is loaded in less than 1 second (around 950 ms) when I access the standard web version via https://libranet.de/community.

I had no idea why the API version takes so much time and the web frontend is so much faster. But I have found the cause.

I also have a very small test node with a barely used test account. On this node and with that account I don't have the issue. So maybe it's a DB performance issue? .... Yes, it is. I just tried it. The DB query takes 2 minutes!

Time: 211224 22:23:44
Query_time: 123.599151  Lock_time: 0.000057  Rows_sent: 20  Rows_examined: 45497893
Rows_affected: 0  Bytes_sent: 283
SET timestamp=1640384624;
SELECT `uri-id` FROM `post-view` WHERE (`gravity` IN (0, 6) AND `private` = 0 AND `network` IN ('dfrn', 'dspr', 'stat', 'apub') AND `parent-author-blocked` = 0 AND `parent-author-hidden` = 0 AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `parent-author-id` AND (`blocked` OR `ignored`)) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 2 AND `uri-id` = `post-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `uri-id` DESC LIMIT 20;

Steps to Reproduce

1. compare the speed of .../api/v1/timelines/public and .../community
2. compare the query time of both DB queries

Actual Result:
The API endpoint is so slow that the apps are giving up while loading the content and therefore don't show any content.

Expected Result:
The API path should be as fast as the path used by the web frontend.

Platform Info

Friendica Version:
2021.12-rc

Friendica Source:
git

PHP version:
7.4

SQL version:
10.3 MariaDB

[AlfredSK]

Comparison of the same query between me (UID 1) on venera.social and me (UID 2) on libranet.de. The difference is huge. Both nodes running on the same machine using the same DB server. The only difference I see is the size of the DB. venera.social is around 26 GB and libranet.de is around 94 GB.

venera.social

MariaDB [friendica]> use venera;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [venera]> SELECT `uri-id` FROM `post-view` WHERE (`gravity` IN (0, 6) AND `private` = 0 AND `network` IN ('dfrn', 'dspr', 'stat', 'apub') AND `parent-author-blocked` = 0 AND `parent-author-hidden` = 0 AND NOT EXISTS (SELECT `cid` FROM `user-contac
t` WHERE `uid` = 1 AND `cid` = `parent-author-id` AND (`blocked` OR `ignored`)) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked`
 AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 1 AND `uri-id` = `post-view`.`uri-id` AND `hidden`) AND
 NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WH
ERE `uid` = 1 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `uri-id` DESC LIMIT 20;
+----------+
| uri-id   |
+----------+
| 15627270 |
| 15627269 |
| 15627268 |
| 15627266 |
| 15627265 |
| 15627264 |
| 15627263 |
| 15627248 |
| 15627240 |
| 15627238 |
| 15627235 |
| 15627234 |
| 15627232 |
| 15627231 |
| 15627230 |
| 15627229 |
| 15627228 |
| 15627227 |
| 15627224 |
| 15627222 |
+----------+
20 rows in set (0.019 sec)

libranet.de

MariaDB [venera]> use friendica;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [friendica]> SELECT `uri-id` FROM `post-view` WHERE (`gravity` IN (0, 6) AND `private` = 0 AND `network` IN ('dfrn', 'dspr', 'stat', 'apub') AND `parent-author-blocked` = 0 AND `parent-author-hidden` = 0 AND NOT EXISTS (SELECT `cid` FROM `user-con
tact` WHERE `uid` = 2 AND `cid` = `parent-author-id` AND (`blocked` OR `ignored`)) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-block
ed` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 2 AND `uri-id` = `post-view`.`uri-id` AND `hidden`) 
AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact`
 WHERE `uid` = 2 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `uri-id` DESC LIMIT 20;
+----------+
| uri-id   |
+----------+
| 61708982 |
| 61708981 |
| 61708970 |
| 61708968 |
| 61708967 |
| 61708966 |
| 61708963 |
| 61708959 |
| 61708958 |
| 61708956 |
| 61708955 |
| 61708948 |
| 61708944 |
| 61708942 |
| 61708941 |
| 61708940 |
| 61708939 |
| 61708937 |
| 61708935 |
| 61708934 |
+----------+
20 rows in set (2 min 6.203 sec)

[AlfredSK]

I'll run a mysqloptimize on the DB to see if it helps with the performance.

[AlfredSK]

Ok. After running mysqloptimize friendica the very same query takes 0.058 seconds instead of 2 minutes. That's impressive but also concerning.

That helped a lot with the global public timeline. But the local public timeline still doesn't load in the apps. 🤔

[annando]

The local timeline is called with this parameter: https://libranet.de/api/v1/timelines/public?local=true

Since this is really fast on my both machines, please have a look at your system. And possibly you can run the slow query with explain to see where it is slow.

[AlfredSK]

The query for https://libranet.de/api/v1/timelines/public is super fast now and the page (JSON) is loading very fast - after I have optimized the database.

But when I access https://libranet.de/api/v1/timelines/public?local=true the page needs more than 3 minutes to load and the query is super slow:

# Query_time: 187.286412  Lock_time: 0.000254  Rows_sent: 20  Rows_examined: 53750931
# Rows_affected: 0  Bytes_sent: 283
SET timestamp=1640517824;
SELECT `uri-id` FROM `post-view` WHERE (`gravity` IN (0, 6) AND `private` = 0 AND `network` IN ('dfrn', 'dspr', 'stat', 'apub') AND `parent-author-blocked` = 0 AND `parent-author-hidden` = 0 AND `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin`) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 0 AND `uri-id` = `post-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `uri-id` DESC LIMIT 20;

With explain:

MariaDB [friendica]> explain SELECT `uri-id` FROM `post-view` WHERE (`gravity` IN (0, 6) AND `private` = 0 AND `network` IN ('dfrn', 'dspr', 'stat', 'apub') AND `parent-author-blocked` = 0 AND `parent-author-hidden` = 0 AND `uri-id` IN (SELECT `uri-id` FROM `post-user` WHERE `origin`) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 0 AND `uri-id` = `post-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `uri-id` DESC LIMIT 20;
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+----------------------------------------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                                                   | key               | key_len | ref                             | rows   | Extra                                        |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+----------------------------------------------+
|    1 | PRIMARY            | parent-post-author | ref    | PRIMARY,blocked_uid                                                                                                                                             | blocked_uid       | 1       | const                           | 591333 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY            | parent-post        | ref    | PRIMARY,author-id                                                                                                                                               | author-id         | 4       | friendica.parent-post-author.id |     23 | Using index                                  |
|    1 | PRIMARY            | post               | ref    | PRIMARY,parent-uri-id,owner-id,author-id,causer-id                                                                                                              | parent-uri-id     | 5       | friendica.parent-post.uri-id    |      1 | Using where                                  |
|    1 | PRIMARY            | causer             | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | friendica.post.causer-id        |      1 | Using where                                  |
|    1 | PRIMARY            | post-thread        | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | friendica.parent-post.uri-id    |      1 | Using index                                  |
|    1 | PRIMARY            | author             | eq_ref | PRIMARY,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate,uid_contact-type,uid_self_contact-type | PRIMARY           | 4       | friendica.post.author-id        |      1 | Using where                                  |
|    1 | PRIMARY            | owner              | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | friendica.post.owner-id         |      1 | Using where                                  |
|    1 | PRIMARY            | post-user          | ref    | uri-id                                                                                                                                                          | uri-id            | 4       | friendica.post.uri-id           |      1 | Using where; Start temporary; End temporary  |
|    7 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,friendica.post.owner-id   |      1 | Using where                                  |
|    6 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,friendica.post.author-id  |      1 | Using where                                  |
|    5 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           |      1 | Using where                                  |
|    4 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           |      1 | Using where                                  |
|    3 | MATERIALIZED       | post-user          | range  | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen,uid_hidden_uri-id                                               | uid_hidden_uri-id | 4       | NULL                            |      2 | Using where; Using index                     |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+----------------------------------------------+
13 rows in set (0.001 sec)

Sorry. No idea why Github is messing with the spaces in the table.

[AlfredSK]

Looks like it is using a temporary table for something (sort?) instead of an index. When I run the same explain on the database of venera.social (same db sever, same machine) the result looks different (no temporary table used, query is super fast).

[AlfredSK]

For a better comparison both results in one comment. Both nodes are on current RC.

libranet.de

+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+----------------------------------------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                                                   | key               | key_len | ref                             | rows   | Extra                                        |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+----------------------------------------------+
|    1 | PRIMARY            | parent-post-author | ref    | PRIMARY,blocked_uid                                                                                                                                             | blocked_uid       | 1       | const                           | 591333 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY            | parent-post        | ref    | PRIMARY,author-id                                                                                                                                               | author-id         | 4       | friendica.parent-post-author.id |     23 | Using index                                  |
|    1 | PRIMARY            | post               | ref    | PRIMARY,parent-uri-id,owner-id,author-id,causer-id                                                                                                              | parent-uri-id     | 5       | friendica.parent-post.uri-id    |      1 | Using where                                  |
|    1 | PRIMARY            | causer             | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | friendica.post.causer-id        |      1 | Using where                                  |
|    1 | PRIMARY            | post-thread        | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | friendica.parent-post.uri-id    |      1 | Using index                                  |
|    1 | PRIMARY            | author             | eq_ref | PRIMARY,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate,uid_contact-type,uid_self_contact-type | PRIMARY           | 4       | friendica.post.author-id        |      1 | Using where                                  |
|    1 | PRIMARY            | owner              | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | friendica.post.owner-id         |      1 | Using where                                  |
|    1 | PRIMARY            | post-user          | ref    | uri-id                                                                                                                                                          | uri-id            | 4       | friendica.post.uri-id           |      1 | Using where; Start temporary; End temporary  |
|    7 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,friendica.post.owner-id   |      1 | Using where                                  |
|    6 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,friendica.post.author-id  |      1 | Using where                                  |
|    5 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           |      1 | Using where                                  |
|    4 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           |      1 | Using where                                  |
|    3 | MATERIALIZED       | post-user          | range  | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen,uid_hidden_uri-id                                               | uid_hidden_uri-id | 4       | NULL                            |      2 | Using where; Using index                     |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+----------------------------------------------+
13 rows in set (0.001 sec)

venera.social

+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+------------------------------+----------+--------------------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                                                   | key               | key_len | ref                          | rows     | Extra                    |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+------------------------------+----------+--------------------------+
|    1 | PRIMARY            | post               | index  | PRIMARY,parent-uri-id,owner-id,author-id,causer-id                                                                                                              | PRIMARY           | 4       | NULL                         |        2 | Using where              |
|    1 | PRIMARY            | <subquery2>        | eq_ref | distinct_key                                                                                                                                                    | distinct_key      | 4       | func                         |        1 |                          |
|    1 | PRIMARY            | post-thread        | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | venera.post.parent-uri-id    |        1 | Using index              |
|    1 | PRIMARY            | author             | eq_ref | PRIMARY,uid_name,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_network_self_lastupdate,uid_lastitem,uid_contact-type,uid_self_contact-type | PRIMARY           | 4       | venera.post.author-id        |        1 | Using where              |
|    1 | PRIMARY            | owner              | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | venera.post.owner-id         |        1 | Using where              |
|    1 | PRIMARY            | causer             | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | venera.post.causer-id        |        1 | Using where              |
|    1 | PRIMARY            | parent-post        | eq_ref | PRIMARY,author-id                                                                                                                                               | PRIMARY           | 4       | venera.post.parent-uri-id    |        1 |                          |
|    1 | PRIMARY            | parent-post-author | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | venera.parent-post.author-id |        1 | Using where              |
|    2 | MATERIALIZED       | post-user          | ALL    | uri-id                                                                                                                                                          | NULL              | NULL    | NULL                         | 13458975 | Using where              |
|    7 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,venera.post.owner-id   |        1 | Using where              |
|    6 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,venera.post.author-id  |        1 | Using where              |
|    5 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                        |        1 | Using where              |
|    4 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                        |        1 | Using where              |
|    3 | MATERIALIZED       | post-user          | range  | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen,uid_hidden_uri-id                                               | uid_hidden_uri-id | 4       | NULL                         |        2 | Using where; Using index |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+------------------------------+----------+--------------------------+
14 rows in set (0.001 sec)

[AlfredSK]

Update: Now the global public timeline is slow again. Looks like the mysqloptimize did help for some time. That query takes more than 150 seconds now. So the problem is back for both public timelines and queries.

[AlfredSK]

I have changed my DB config to use more or less the default settings for MariaDB on Ubuntu. But that didn't improve the performance of these two queries. So I think it's not a config issue.

[annando]

Your optimizer does weird things. The explain looks at pirati.ca like this:

+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+--------------------------------+------+---------------------------------------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                                                   | key               | key_len | ref                            | rows | Extra                                       |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+--------------------------------+------+---------------------------------------------+
|    1 | PRIMARY            | post               | index  | PRIMARY,parent-uri-id,owner-id,author-id,causer-id                                                                                                              | PRIMARY           | 4       | NULL                           |   40 | Using where                                 |
|    1 | PRIMARY            | post-user          | ref    | uri-id                                                                                                                                                          | uri-id            | 4       | piratica.post.uri-id           |    1 | Using where; Start temporary; End temporary |
|    1 | PRIMARY            | post-thread        | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | piratica.post.parent-uri-id    |    1 | Using index                                 |
|    1 | PRIMARY            | author             | eq_ref | PRIMARY,pending_uid,blocked_uid,uid_name,uid_rel_network_poll,uid_network_batch,uid_lastitem,uid_network_self_lastupdate,uid_contact-type,uid_self_contact-type | PRIMARY           | 4       | piratica.post.author-id        |    1 | Using where                                 |
|    1 | PRIMARY            | owner              | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | piratica.post.owner-id         |    1 | Using where                                 |
|    1 | PRIMARY            | causer             | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | piratica.post.causer-id        |    1 | Using where                                 |
|    1 | PRIMARY            | parent-post        | eq_ref | PRIMARY,author-id                                                                                                                                               | PRIMARY           | 4       | piratica.post.parent-uri-id    |    1 |                                             |
|    1 | PRIMARY            | parent-post-author | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | piratica.parent-post.author-id |    1 | Using where                                 |
|    7 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,piratica.post.owner-id   |    1 | Using where                                 |
|    6 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,piratica.post.author-id  |    1 | Using where                                 |
|    5 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                          | 2067 | Using where                                 |
|    4 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                          | 2067 | Using where                                 |
|    3 | MATERIALIZED       | post-user          | range  | uid_uri-id,uri-id,uid_unseen_contactid,parent-uri-id_uid,author-id_uid,uid_contactid,uid_unseen,uid_hidden_uri-id                                               | uid_hidden_uri-id | 4       | NULL                           |    2 | Using where; Using index                    |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+--------------------------------+------+---------------------------------------------+

squeet.me:

+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+--------------------------------+------+---------------------------------------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                                                   | key               | key_len | ref                            | rows | Extra                                       |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+--------------------------------+------+---------------------------------------------+
|    1 | PRIMARY            | post               | index  | PRIMARY,parent-uri-id,owner-id,author-id,causer-id                                                                                                              | PRIMARY           | 4       | NULL                           |   40 | Using where                                 |
|    1 | PRIMARY            | post-user          | ref    | uri-id                                                                                                                                                          | uri-id            | 4       | squeetme.post.uri-id           |    1 | Using where; Start temporary; End temporary |
|    1 | PRIMARY            | post-thread        | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | squeetme.post.parent-uri-id    |    1 | Using index                                 |
|    1 | PRIMARY            | author             | eq_ref | PRIMARY,pending_uid,blocked_uid,uid_name,uid_rel_network_poll,uid_network_batch,uid_lastitem,uid_network_self_lastupdate,uid_contact-type,uid_self_contact-type | PRIMARY           | 4       | squeetme.post.author-id        |    1 | Using where                                 |
|    1 | PRIMARY            | owner              | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | squeetme.post.owner-id         |    1 | Using where                                 |
|    1 | PRIMARY            | causer             | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | squeetme.post.causer-id        |    1 | Using where                                 |
|    1 | PRIMARY            | parent-post        | eq_ref | PRIMARY,author-id                                                                                                                                               | PRIMARY           | 4       | squeetme.post.parent-uri-id    |    1 |                                             |
|    1 | PRIMARY            | parent-post-author | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | squeetme.parent-post.author-id |    1 | Using where                                 |
|    7 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,squeetme.post.owner-id   |    1 | Using where                                 |
|    6 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,squeetme.post.author-id  |    1 | Using where                                 |
|    5 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                          |    1 | Using where                                 |
|    4 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                          |    1 | Using where                                 |
|    3 | MATERIALIZED       | post-user          | range  | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen,uid_hidden_uri-id                                               | uid_hidden_uri-id | 4       | NULL                           |    2 | Using where; Using index                    |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+--------------------------------+------+---------------------------------------------+

[AlfredSK]

I haven't changed anything on how the query optimizer should work. The only important config change compared to the default config is that I'm using a big InnoDB buffer pool.
What version of MariaDB are you using?

[annando]

10.1.48 on both machines.

[AlfredSK]

Wow! That's very old. 😬 I'm on MariaDB 10.3.32 and I just considered to upgrade to the current old stable version 10.5.x.

[AlfredSK]

Ok. Before I go to bed I upgraded MariaDB to the latest stable version 10.6.5 and left the config mostly on default values.

It didn't change anything. The queries for both public timelines are still super slow.

# Query_time: 125.672875  Lock_time: 0.000185  Rows_sent: 30  Rows_examined: 45640879
# Rows_affected: 0  Bytes_sent: 0
use friendica;
SET timestamp=1640563583;
SELECT `uri-id` FROM `post-view` WHERE (`gravity` IN (0, 6) AND `private` = 0 AND `network` IN ('dfrn', 'dspr', 'stat', 'apub') AND `parent-author-blocked` = 0 AND `parent-author-hidden` = 0 AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `parent-author-id` AND (`blocked` OR `ignored`)) AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 2 AND `uri-id` = `post-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 2 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `uri-id` DESC LIMIT 30;

With explain on MariaDB 10.6.5

+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+---------------------------------------------------------------------+
| id   | select_type        | table              | type   | possible_keys                                                                                                                                                   | key               | key_len | ref                             | rows   | Extra                                                               |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+---------------------------------------------------------------------+
|    1 | PRIMARY            | parent-post-author | range  | PRIMARY,blocked_uid                                                                                                                                             | blocked_uid       | 1       | NULL                            | 567997 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | PRIMARY            | parent-post        | ref    | PRIMARY,author-id                                                                                                                                               | author-id         | 4       | friendica.parent-post-author.id | 17     | Using index                                                         |
|    1 | PRIMARY            | post               | ref    | parent-uri-id,owner-id,author-id,causer-id                                                                                                                      | parent-uri-id     | 5       | friendica.parent-post.uri-id    | 1      | Using where                                                         |
|    1 | PRIMARY            | causer             | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | friendica.post.causer-id        | 1      | Using where                                                         |
|    1 | PRIMARY            | post-thread        | eq_ref | PRIMARY                                                                                                                                                         | PRIMARY           | 4       | friendica.parent-post.uri-id    | 1      | Using index                                                         |
|    1 | PRIMARY            | author             | eq_ref | PRIMARY,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate,uid_contact-type,uid_self_contact-type | PRIMARY           | 4       | friendica.post.author-id        | 1      | Using where                                                         |
|    1 | PRIMARY            | owner              | eq_ref | PRIMARY,blocked_uid                                                                                                                                             | PRIMARY           | 4       | friendica.post.owner-id         | 1      | Using where                                                         |
|    7 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,friendica.post.owner-id   | 1      | Using where                                                         |
|    6 | DEPENDENT SUBQUERY | user-contact       | eq_ref | PRIMARY,cid                                                                                                                                                     | PRIMARY           | 7       | const,friendica.post.author-id  | 1      | Using where                                                         |
|    5 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           | 602    | Using where                                                         |
|    4 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           | 602    | Using where                                                         |
|    3 | MATERIALIZED       | post-user          | range  | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen,uid_hidden_uri-id                                               | uid_hidden_uri-id | 4       | NULL                            | 190957 | Using where; Using index                                            |
|    2 | MATERIALIZED       | user-contact       | ref    | PRIMARY,uri-id_uid,cid                                                                                                                                          | PRIMARY           | 3       | const                           | 602    | Using where                                                         |
+------+--------------------+--------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+---------------------------------+--------+---------------------------------------------------------------------+
13 rows in set (0.005 sec)

[AlfredSK]

I guess this is another example that some queries we're using don't get along well with MariaDB 10.3 and later versions.

After changing my DB config to use an ancient optimizer algorithm the queries are very fast now.

I'll close this one and link it to the other DB performance issue.

[AlfredSK]

See also #11465