High database usage by `get_auth_chain_ids_chains` after no particular event

[n-peugnet]

Description

The CPU usage of my server started to increase after no particular action on my end. After digging a little, I did find that the get_auth_chain_ids_chains transaction started to take a lot more time that previously.

Synapse exporter:

Postgres exporter:

Steps to reproduce

• Run synapse for a long time 😅

Homeserver

club1.fr

Synapse Version

1.103.0

Installation Method

Debian packages from packages.matrix.org

Database

PostgreSQL single server, restored from a backup

Workers

Single process

Platform

• Debian 11 Bullseye
• Linux club1.fr 5.10.0-31-amd64 #1 SMP Debian 5.10.221-1 (2024-07-14) x86_64 GNU/Linux
• No VM, no Containers, Bare metal

Configuration

Definitely some unusual config:

# Message retention policy at the server level.
#
# Room admins and mods can define a retention period for their rooms using the
# 'm.room.retention' state event, and server admins can cap this period by setting
# the 'allowed_lifetime_min' and 'allowed_lifetime_max' config options.
#
# If this feature is enabled, Synapse will regularly look for and purge events
# which are older than the room's maximum retention period. Synapse will also
# filter events received over federation so that events that should have been
# purged are ignored and not stored again.
#
retention:
  # The message retention policies feature is disabled by default. Uncomment the
  # following line to enable it.
  #
  enabled: true

  # Default retention policy. If set, Synapse will apply it to rooms that lack the
  # 'm.room.retention' state event. Currently, the value of 'min_lifetime' doesn't
  # matter much because Synapse doesn't take it into account yet.
  #
  default_policy:
    min_lifetime: 1d
    max_lifetime: 3y

media_retention:
  local_media_lifetime: 2y
  remote_media_lifetime: 90d

# When this option is enabled, the room "complexity" will be checked before a user
# joins a new remote room. If it is above the complexity limit, the server will
# disallow joining, or will instantly leave.
limit_remote_rooms:
  enabled: true
  complexity: 10
  complexity_error: "Désolé, cette room est trop complèxe pour le serveur CLUB1."
  admins_can_join: false

# disable presence completely
presence:
  enabled: false

Relevant log output

https://static.club1.fr/nicolas/random-other/log-synapse-2024-07-19.log

Anything else that would be useful to know?

No response

[n-peugnet]

I enabled DEBUG logging for synapse.storage.SQL, and I can sew quite a few blocks like this one:

2024-07-22 21:47:14,401 - synapse.storage.SQL - 493 - DEBUG - GET-3728057 - [SQL time] {get_auth_chain_ids_chains-c52929} 6.112671 sec
2024-07-22 21:47:14,420 - synapse.storage.SQL - 467 - DEBUG - GET-3728057 - [SQL] {get_auth_chain_ids_chains-c52929} WITH RECURSIVE links(chain_id) AS ( SELECT DISTINCT origin_chain_id FROM event_auth_chain_links WHERE origin_chain_id = ANY(?) UNION SELECT target_chain_id FROM event_auth_chain_links INNER JOIN links ON (chain_id = origin_chain_id) ) SELECT origin_chain_id, origin_sequence_number, target_chain_id, target_sequence_number FROM links INNER JOIN event_auth_chain_links ON (chain_id = origin_chain_id)
2024-07-22 21:47:14,421 - synapse.storage.SQL - 472 - DEBUG - GET-3728057 - [SQL values] {get_auth_chain_ids_chains-c52929} [[1733489, 448752, 1733506, 448754, 1602442, 1733521, 1602450, 1733538, 1602478, 454457, 448763, 454458, 448771, 1733609, 1733613, 448784, 1471551, 448794, 1602642, 448802, 1602710, 448812, 1602732, 1602750, 1733899, 1733903, 1602838, 1733921, 1602890, 448847, 448849, 448852, 448856, 1734028, 448861, 448864, 1734088, 1734090, 1734117, 1734171, 1734196, 448892, 1734206, 1734208, 1734209, 1603139, 1603142, 1734218, 1734224, 1603159, 1603169, 1603170, 1734291, 1603236, 1603266, 1734340, 448920, 1603271, 685786, 1734363, 1341158, 448931, 1341182, 1603335, 1734438, 1734446, 685879, 1341242, 1341244, 448944, 685889, 1734473, 448947, 1734479, 1603412, 1341269, 685909, 685920, 685921, 685923, 1603441, 448955, 685942, 685950, 685965, 1734545, 685986, 685988, 1734575, 686000, 686002, 686004, 1341378, 686022, 1603537, 1734638, 1734642, 448982, 1603596, 1734674, 1341467, 1603615, 1603623, 1603625, 448991, 1734700, 448994, 1341503, 1341504, 1603678, 1734763, 1734765, 449007, 1734784, 1603728, 449013, 449018, 1734837, 1341623, 1603777, 1734870, 1734878, 1341673, 1734893, 1734903, 1734906, 449033, 1603839, 1734915, 449036, 1734928, 1734930, 449038, 1603874, 1341753, 1341763, 1341767, 1603914, 1341775, 1734998, 1360858, 1341794, 1341800, 449057, 1735048, 1735052, 1735068, 1735069, 1604001, 1735077, 1735085, 1604044, 1604061, 1604078, 1341947, 1604113, 1735190, 1735211, 1604164, 449103, 1735268, 1735280, 1735293, 1342079, 1604230, 449112, 1735334, 449119, 1735369, 1735375, 1735377, 449128, 1735394, 1604328, 449131, 1342195, 1735418, 1604361, 1735444, 1735453, 1735480, 1735484, 1360953, 1735502, 1735516, 1735533, 1735534, 1106879, 1604485, 1604487, 1735559, 1735585, 1735594, 449171, 1735606, 1604542, 1342400, 1342405, 1604553, 1342412, 1604567, 1735646, 1735653, 1604583, 449183, 1342455, 1735687, 1342472, 1735711, 449195, 1342557, 1735784, 1361016, 1080445, 1735808, 454547, 1735824, 449216, 449217, 1735841, 1735843, 1604779, 1080498, 1735862, 1735867, 1342659, 1735876, 449227, 1735889, 1604819, 1735893, 1080548, 1735919, 1735933, 1604874, 1735951, 1735954, 1736001, 1736007, 1736010, 1736013, 1736032, 1736053, 1604989, 687504, 687511, 1605032, 1605038, 1605059, 687567, 1342937, 1361088, 687616, 1605134, 687631, 449293, 1605147, 1343025, 1605179, 1605183, 687685, 687694, 687711, 687726, 449310, 1605245, 687745, 1605261, 449318, 1343140, 1626844, 687792, 1343165, 1343176, 1361133, 687826, 1343188, 449336, 1605396, 1605404, 1605426, 1605428, 1343285, 687929, 1605438, 1343313, 1605459, 1605460, 687967, 687976, 687984, 449367, 1343379, 1343426, 1605598, 454583, 449399, 1474621, 1361209, 449411, 1605767, 1343662, 449425, 1605814, 1343702, 1343719, 1605902, 1343783, 1605941, 1343800, 1361264, 1343877, 449470, 449483, 1606133, 1343990, 1344038, 1606212, 449509, 1344114, 1606262, 1344120, 1606286, 1344156, 1344163, 449526, 1606320, 449534, 1606360, 1344239, 1606425, 1606438, 1344294, 1344300, 1606446, 1606455, 1100845, 449556, 449561, 1606534, 1344410, 1344415, 1344456, 449583, 1606613, 449586, 1361393, 1344520, 449595, 449596, 1344532, 1606679, 449599, 449603, 449605, 1606756, 1606759, 1344616, 1606770, 1344673, 1344686, 449628, 1344694, 1606849, 1606876, 1344761, 1606912, 449649, 1606938, 1606939, 1606943, 1606953, 1344814, 1606964, 1231202, 1344855, 449667, 1607033, 1607036, 449672, 1607056, 1607060, 449674, 1607067, 1213854, 1344940, 449679, 1213967, 449698, 1345056, 1345094, 1345105, 1345114, 449718, 1345143, 970756, 1345174, 1345182, 1361533, 454650, 1083080, 454653, 1345281, 449747, 1345300, 1345388, 1345392, 449769, 449789, 690171, 690174, 690176, 970829, 1345583, 690284, 690377, 690407, 454674, 690467, 449877, 1345963, 449882, 1345967, 1346004, 1346022, 1346030, 449895, 1346050, 1346079, 449905, 1346102, 1346124, 1346129, 1346131, 1346134, 454688, 1739369, 1346161, 1739393, 1346185, 1739410, 1346197, 449929, 455646, 1346223, 449939, 1346276, 454695, 1739565, 1739582, 1739599, 1739633, 1739642, 1739647, 1739661, 691090, 1346458, 1739684, 691108, 1215442, 1215443, 1215444, 1215445, 1739734, 1215447, 1215448, 1215446, 691180, 1608696, 691204, 971035, 1739794, 1608739, 1608745, 1739820, 450012, 691263, 1346629, 1346644, 1739860, 691285, 1215580, 1215585, 1215587, 691307, 1215597, 1739895, 1739896, 1608823, 450027, 691341, 1608865, 691375, 691390, 450041, 1740000, 1740003, 1346794, 1346803, 1608953, 1740047, 1740064, 1608999, 1740076, 450061, 1346876, 1478006, 450081, 454721, 1609152, 450090, 1347019, 1347041, 450099, 1347069, 1347087, 1609256, 1347132, 1609276, 1609282, 1216075, 450122, 450123, 691842, 691862, 1216154, 691869, 1347231, 691882, 1609390, 1085105, 1216236, 1609456, 1347327, 1609471, 1347347, 1347350, 1085230, 1622486, 1347393, 1609560, 1085297, 1347465, 1609616, 1609624, 1609627, 1347486, 1347489, 1216443, 1347517, 1347528, 1347546, 450202, 450206, 1347633, 1347661, 450222, 1347690, 450228, 1231783, 450239, 1347811, 450250, 450254, 1216770, 1347843, 1347885, 1347904, 1347913, 1347937, 450275, 450276, 1347968, 450280, 1347986, 1347987, 1348027, 1348042, 450314, 450315, 1348195, 450331, 1348237, 1217191, 1348287, 1348293, 1348295, 450348, 450354, 1757304, 1348357, 1362166, 1348449, 1217416, 450393, 454784, 450403, 1348600, 1217553, 450411, 1348664, 1348673, 971452, 450425, 1348698, 1217631, 1348718, 450458, 454796, 971497, 1348956, 450478, 1348966, 450480, 1217934, 1232037, 450493, 1349067, 450516, 450518, 450526, 1218150, 450548, 450549, 1218301, 1218302, 450560, 450567, 454818, 450571, 1218377, 450584, 450589, 1349543, 450593, 1218500, 450601, 1349593, 450609, 1349656, 1349660, 1218596, 1349691, 450623, 1232171, 1349717, 1349753, 454835, 1349855, 454838, 450677, 450678, 455676, 1350019, 1218955, 1350041, 1350096, 1219037, 1219049, 1350121, 1350180, 1350190, 1350196, 1350203, 1350222, 450730, 563814, 1350251, 1350279, 1219213, 563871, 563894, 1350337, 450751, 563922, 1350355, 563931, 563939, 563948, 563983, 563989, 1350430, 564008, 564012, 564066, 454861, 450784, 450785, 564078, 564092, 1350528, 1350529, 564101, 564104, 1350544, 1623115, 1350559, 1219494, 1219519, 564163, 564176, 564182, 564189, 564194, 564201, 564206, 564209, 455681, 564219, 1350656, 564231, 564253, 1350689, 1219640, 1219652, 564294, 564314, 1350751, 564322, 564329, 1219695, 564341, 1219703, 454872, 564393, 564414, 1350852, 564433, 1350875, 1219818, 1350894, 564464, 564478, 564479, 1219853, 564494, 450869, 1350942, 1613103, 450875, 564548, 564553, 1350989, 564558, 564576, 1351014, 564583, 450890, 564606, 564619, 450895, 564652, 454885, 1613254, 450905, 1351113, 450912, 1613297, 1613299, 1220096, 454888, 564762, 1351209, 1613372, 564812, 1220196, 1613423, 1220230, 1613469, 564917, 564930, 1232505, 564949, 1613526, 450960, 564962, 1351407, 1220343, 564988, 1613583, 565023, 565031, 1351466, 565034, 450976, 565042, 1351476, 1351493, 565070, 565081, 565085, 1220449, 454902, 1613692, 1613703, 1220508, 454905, 1613751, 1351638, 1613788, 1623332, 1351703, 1613862, 451026, 1351730, 451032, 1613920, 1351808, 1613984, 1613989, 565449, 565451, 1614042, 1351901, 1614045, 565473, 454917, 1614080, 1614081, 1614095, 565523, 565543, 565555, 1614134, 565570, 1220934, 565589, 451091, 565631, 565643, 565649, 1614238, 565680, 1614257, 451105, 451110, 455693, 1221081, 454928, 1221128, 565769, 565778, 451125, 565793, 565825, 565835, 565837, 565848, 1614428, 451138, 1614444, 451141, 565876, 565883, 1745538, 1614472, 451148, 565913, 1745564, 1745582, 1745619, 1221349, 1614569, 1623489, 1221393, 1614618, 1614649, 1614650, 1352514, 451191, 1745786, 1352590, 1745820, 451202, 1614760, 451204, 1221549, 1352627, 1745845, 1352633, 1614781, 1745853, 1614794, 1745871, 1614800, 1352664, 1614821, 1745912, 451220, 1745941, 1221696, 1352771, 1745997, 1221710, 1221733, 1352841, 1352844, 1746075, 1746076, 1221792, 1352889, 454956, 1746139, 1746140, 451265, 1746172, 451276, 1353000, 1221947, 1353033, 1232834, 1221969, 451291, 1222015, 1746314, 1353129, 1353142, 1353145, 451311, 1746391, 451317, 451323, 1746436, 451325, 1353227, 1746469, 1746476, 1353270, 1746494, 451338, 1757501, 1222266, 454974, 1353347, 1353362, 1746581, 1353369, 1746587, 451364, 1746643, 1353443, 1746668, 1353477, 1746714, 1222440, 451383, 1222447, 1353533, 1746755, 454983, 1353574, 960359, 451395, 1746795, 1353586, 1353601, 1353602, 451402, 451403, 960430, 451414, 1746897, 451417, 960474, 960480, 451419, 1746915, 451421, 1222651, 960511, 960544, 1747045, 451446, 451448, 451450, 1747080, 451453, 451455, 1222847, 454997, 451466, 1747167, 1747195, 451475, 451479, 1747217, 1747226, 451482, 1747232, 1623809, 1747263, 1747274, 1223002, 1747299, 1747319, 1747322, 451503, 451505, 451506, 1354147]]

Could this be a symptom of GHSA-3h7q-rfh9-xm4v ?

EDIT: it does not seem so, I just updated to v1.111 and the issue persists.

[n-peugnet]

It started to become worse tonight:

[n-peugnet]

As it seems the problem came from intensive recursive SQL queries, I tried to issue a reindex (verbose) database synapse; (07/23 13h) after the Synapse upgrade to v1.111, to make sure the indices were as optimized as possible, but it did not solve anything. Quite the opposite:

The get_auth_chain_difference_chains increase is probably due to the Synapse upgrade from v1.103 to v1.111, as it happened the 22 at 22:12:08.
EDIT: I just saw that this particular issue is tracked in #17129

[n-peugnet]

As my best clue was the logs shown in #17470 (comment), I decided to find what rooms were affected. So I used the following query where 444066 can be replaced by any of the value returned by the long query :

SELECT room_id
FROM event_auth_chains AS chains
INNER JOIN events ON (chains.event_id = events.event_id)
WHERE chain_id = 444066;

In my case, I had multiple long queries all linking to !lAoVmVifHHtoeOAmHO:grapheneos.org, so I decided to delete is using the admin API. And it seems everything went back to normal.

EDIT: hmm... not really, there are a lot of other rooms that show this behaviour
EDIT2: after deleting the few rooms that showed up in long queries it seems the situation is now under control, but I am still waiting for a large room to be purged.

[parisni]

same behavior on my side. Running v1.106.0

[parisni]

I still have this issue, here is the query / plan

https://explain.dalibo.com/plan/271d35b19fe84g32#plan

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Merge Join  (cost=575263.44..695224.02 rows=802392 width=32)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|   Merge Cond: (event_auth_chain_links.origin_chain_id = links.chain_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   CTE links                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|     ->  Recursive Union  (cost=0.43..524411.88 rows=401196 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|           ->  Unique  (cost=0.43..4029.96 rows=1996 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|                 ->  Index Only Scan using event_auth_chain_links_origin_index on event_auth_chain_links event_auth_chain_links_1  (cost=0.43..4024.97 rows=1997 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|                       Index Cond: (origin_chain_id = ANY ('{252142,252143,317683,252144,252145,252146,252147,252148,252149,252150,252151,252152,252153,252154,252155,252156,252157,317698,252158,252159,252160,252161,252162,317704,252163,252164,252165,317708,252166,317710,252167,317712,252168,252169,252170,252171,317717,252173,252174,252175,252176,252177,252178,252179,317725,317726,317727,252183,252184,252185,252186,252187,252188,252189,252190,252191,252192,252193,317739,252195,252196,252197,252198,252199,252200,252201,252202,252203,252204,317750,317751,252207,252208,317754,252210,252211,252212,252213,252214,252215,252216,252217,252218,252219,252220,252221,252222,252223,317769,317770,317777,317792,317797,317801,317804,317809,317821,317827,317833,317843,317849,317854,252324,252325,252326,252327,252328,252329,252330,252331,252332,252333,252334,252335,252336,252337,252338,252339,252340,252341,317878,317879,252342,252343,317882,317883,252344,317885,252345,252346,317888,252347,252348,317891,252349,317893,252350,252351,252352,252353,317898,252354,252355,252356,252357,252358,252359,317905,252361,252362,252363,252364,252365,317911,252367,252368,252369,252370,252371,252372,252373,252374,317920,252376,252377,252378,252379,252380,317926,252382,317928,317929,252385,252386,252387,252388,252389,252390,252391,252392,252393,252394,252395,252396,317942,252398,252399,252400,252401,252402,252403,252404,252405,317951,252407,252408,252409,252410,252411,252412,252413,252414,252415,252416,252417,317963,252419,252420,252421,252422,252423,317972,317977,317978,317981,317986,317991,317992,317993,318000,318004,318007,318015,318026,318039,318045,318052,318053,318071,318092,318109,318114,318125,318139,318142,318143,318145,318146,252611,252612,252613,252614,252615,252616,252617,252618,252619,252620,252621,252622,252623,252624,252625,252626,318163,252627,252628,252629,252630,252631,252632,252633,252634,252635,252636,252637,252638,252639,318177,252640,252641,252642,318181,252643,252644,252645,252646,252647,252648,252649,252650,318190,252651,252652,318193,252653,252654,252655,252656,252657,252658,252659,252660,252661,252662,252663,252664,252665,252666,252667,252668,252669,252670,252671,252672,252673,252674,252675,252676,252677,252678,252679,252680,252681,252682,252683,252684,252685,252686,252687,252688,252689,252690,252691,252692,252693,252694,252695,252696,252697,252698,252699,252700,252701,252702,252703,252704,252705,252706,252707,252708,252709,252710,318262,318269,318272,318278,318280,318287,318300,318311,318319,318328,318335,318336,318337,318346,252811,252812,252813,252814,252815,318352,252816,252817,252818,252819,252820,252821,252822,318360,252823,252824,252825,252826,252827,252828,252829,252830,318369,252831,252832,252833,252834,252835,252836,252837,252838,252839,252840,252841,318381,252842,252843,318384,252844,318386,252845,318388,252846,252847,318391,252848,252849,318394,252850,252851,252852,252853,252854,252855,252856,252857,252858,318404,252860,318406,252862,318408,318409,252865,252866,318412,252868,252869,252870,252871,252872,252873,252874,252875,252876,252877,252878,252879,318425,252881,252882,318428,252884,252885,252886,252887,252888,318434,252890,252891,252892,252893,252894,252895,252896,252897,252898,252899,252900,252901,252902,252903,318449,252905,252906,252907,252908,318454,252910,318456,318459,318463,318467,318487,318493,318494,318496,318498,318509,318514,318515,318539,318541,318552,318553,318559,318578,318580,318588,318593,318594,318608,318615,318619,318631,253111,253112,253113,253114,253115,253116,253117,253118,253119,253120,253121,253122,253123,253124,318661,318662,253125,318664,253126,318666,318667,318668,253127,253128,318671,253129,253130,253131,318675,253132,253133,253134,253135,318680,318681,318682,253138,253139,253140,318686,253142,253143,253144,253145,253146,253147,253148,253149,253150,253151,253152,253153,253154,253155,253156,253157,253158,253159,253160,253161,253162,318708,253164,318710,253166,253167,253168,253169,253170,253171,253172,253173,253174,318720,253176,253177,253178,253179,253180,253181,318727,318728,253184,253185,253186,253187,253188,253189,253190,253191,253192,253193,253194,253195,253196,253197,318743,318744,253200,253201,253202,253203,253204,253205,253206,253207,253208,253209,253210,318768,318813,318818,318821,318823,318827,318829,318831,318842,318844,253311,253312,253313,253314,318851,253315,318853,253316,253317,253318,253319,253320,253321,253322,253323,253324,253325,253326,253327,253328,253329,253330,253331,318870,318871,253332,253333,253334,318875,253335,318877,318878,253336,253337,253338,253339,253340,318884,253341,253342,253343,253344,253345,253346,253347,318892,253348,253349,253350,253351,253352,318898,253354,253355,253356,253357,253358,253359,318905,253361,253362,253363,253364,253365,253366,253367,253368,253369,253370,253371,253372,253373,253374,253375,253376,253377,253378,253379,253380,318926,253382,253383,253384,253385,253386,253387,253388,253389,318935,253391,253392,318938,253394,253395,253396,253397,253398,253399,253400,253401,253402,253403,253404,318950,253406,253407,253408,253409,253410,318956,318968,318971,318972,318973,318974,318975,318977,318983,318989,318990,318997,319008,319012,319024,319034,319035,319038,253511,319048,253512,319050,253513,253514,319053,253515,253516,253517,253518,253519,253520,253521,253522,253523,319063,253524,319065,253525,253526,253527,253528,253529,253530,253531,253532,319074,253533,253534,319077,319078,253535,253536,253537,319082,253538,253539,253540,319086,253542,253543,253544,253545,319091,253547,253548,253549,253550,253551,253552,253553,253554,253555,253556,319102,253558,253559,253560,253561,253562,319108,253564,253565,253566,253567,253568,253569,253570,253571,253572,253573,253574,253575,253576,253577,253578,253579,253580,253581,253582,253583,253584,253585,253586,253587,253588,253589,253590,253591,253592,253593,253594,253595,253596,253597,319143,253599,253600,253601,319147,253603,319149,253605,253606,253607,253608,253609,253610,319166,319173,319178,319182,319184,319231,319239,319240,319245,253711,253712,253713,319250,253714,253715,253716,253717,253718,253719,253720,253721,253722,253723,253724,253725,253726,253727,253728,253729,253730,253731,319269,253732,253733,253734,253735,253736,319275,253737,253738,253739,319279,253740,253741,253742,253743,319285,253744,253745,253746,253747,253748,253749,319292,253750,253751,253752,253753,253754,253755,253756,253757,253758,253759,319303,253760,253761,253762,253763,253764,253765,253766,319311,253767,253768,253769,253770,253771,253772,253773,253774,253775,253776,253777,253778,253779,253780,319326,319327,253783,253784,253785,253786,253787,253788,319334,253790,253791,253792,253793,253794,253795,319341,319342,253798,253799,253800,253801,253802,319348,253804,253805,319351,253807,319353,253809,253810,319356,319357,319361,319364,319370,319374,319379,319384,319396,319397,319406,319408,319411,319419,319421,319428,319435,319438,319440,319441,319450,319460,319461,319462,319463,319472,319482,319483,319490,319499,319539,319547,254011,254012,254013,254014}'::integer[])) |
|           ->  Nested Loop  (cost=0.43..51235.80 rows=39920 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|                 ->  WorkTable Scan on links links_1  (cost=0.00..399.20 rows=19960 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|                 ->  Index Only Scan using event_auth_chain_links_idx on event_auth_chain_links event_auth_chain_links_2  (cost=0.43..2.53 rows=2 width=16)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|                       Index Cond: (origin_chain_id = links_1.chain_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   ->  Index Scan using event_auth_chain_links_origin_index on event_auth_chain_links  (cost=0.43..100443.18 rows=2591584 width=32)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   ->  Materialize  (cost=50851.13..52857.11 rows=401196 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|         ->  Sort  (cost=50851.13..51854.12 rows=401196 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|               Sort Key: links.chain_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|               ->  CTE Scan on links  (cost=0.00..8023.92 rows=401196 width=8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| JIT:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   Functions: 14                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|   Options: Inlining true, Optimization true, Expressions true, Deforming true      

explain WITH RECURSIVE links(chain_id) AS ( SELECT DISTINCT origin_chain_id FROM event_auth_chain_links WHERE origin_chain_id = ANY(ARRAY[252142,252143,317683,252144,252145,252146,252147,252148,252149,252150,252151,252152,252153,252154,252155,252156,252157,317698,252158,252159,252160,252161,252162,317704,252163,252164,252165,317708,252166,317710,252167,317712,252168,252169,252170,252171,317717,252173,252174,252175,252176,252177,252178,252179,317725,317726,317727,252183,252184,252185,252186,252187,252188,252189,252190,252191,252192,252193,317739,252195,252196,252197,252198,252199,252200,252201,252202,252203,252204,317750,317751,252207,252208,317754,252210,252211,252212,252213,252214,252215,252216,252217,252218,252219,252220,252221,252222,252223,317769,317770,317777,317792,317797,317801,317804,317809,317821,317827,317833,317843,317849,317854,252324,252325,252326,252327,252328,252329,252330,252331,252332,252333,252334,252335,252336,252337,252338,252339,252340,252341,317878,317879,252342,252343,317882,317883,252344,317885,252345,252346,317888,252347,252348,317891,252349,317893,252350,252351,252352,252353,317898,252354,252355,252356,252357,252358,252359,317905,252361,252362,252363,252364,252365,317911,252367,252368,252369,252370,252371,252372,252373,252374,317920,252376,252377,252378,252379,252380,317926,252382,317928,317929,252385,252386,252387,252388,252389,252390,252391,252392,252393,252394,252395,252396,317942,252398,252399,252400,252401,252402,252403,252404,252405,317951,252407,252408,252409,252410,252411,252412,252413,252414,252415,252416,252417,317963,252419,252420,252421,252422,252423,317972,317977,317978,317981,317986,317991,317992,317993,318000,318004,318007,318015,318026,318039,318045,318052,318053,318071,318092,318109,318114,318125,318139,318142,318143,318145,318146,252611,252612,252613,252614,252615,252616,252617,252618,252619,252620,252621,252622,252623,252624,252625,252626,318163,252627,252628,252629,252630,252631,252632,252633,252634,252635,252636,252637,252638,252639,318177,252640,252641,252642,318181,252643,252644,252645,252646,252647,252648,252649,252650,318190,252651,252652,318193,252653,252654,252655,252656,252657,252658,252659,252660,252661,252662,252663,252664,252665,252666,252667,252668,252669,252670,252671,252672,252673,252674,252675,252676,252677,252678,252679,252680,252681,252682,252683,252684,252685,252686,252687,252688,252689,252690,252691,252692,252693,252694,252695,252696,252697,252698,252699,252700,252701,252702,252703,252704,252705,252706,252707,252708,252709,252710,318262,318269,318272,318278,318280,318287,318300,318311,318319,318328,318335,318336,318337,318346,252811,252812,252813,252814,252815,318352,252816,252817,252818,252819,252820,252821,252822,318360,252823,252824,252825,252826,252827,252828,252829,252830,318369,252831,252832,252833,252834,252835,252836,252837,252838,252839,252840,252841,318381,252842,252843,318384,252844,318386,252845,318388,252846,252847,318391,252848,252849,318394,252850,252851,252852,252853,252854,252855,252856,252857,252858,318404,252860,318406,252862,318408,318409,252865,252866,318412,252868,252869,252870,252871,252872,252873,252874,252875,252876,252877,252878,252879,318425,252881,252882,318428,252884,252885,252886,252887,252888,318434,252890,252891,252892,252893,252894,252895,252896,252897,252898,252899,252900,252901,252902,252903,318449,252905,252906,252907,252908,318454,252910,318456,318459,318463,318467,318487,318493,318494,318496,318498,318509,318514,318515,318539,318541,318552,318553,318559,318578,318580,318588,318593,318594,318608,318615,318619,318631,253111,253112,253113,253114,253115,253116,253117,253118,253119,253120,253121,253122,253123,253124,318661,318662,253125,318664,253126,318666,318667,318668,253127,253128,318671,253129,253130,253131,318675,253132,253133,253134,253135,318680,318681,318682,253138,253139,253140,318686,253142,253143,253144,253145,253146,253147,253148,253149,253150,253151,253152,253153,253154,253155,253156,253157,253158,253159,253160,253161,253162,318708,253164,318710,253166,253167,253168,253169,253170,253171,253172,253173,253174,318720,253176,253177,253178,253179,253180,253181,318727,318728,253184,253185,253186,253187,253188,253189,253190,253191,253192,253193,253194,253195,253196,253197,318743,318744,253200,253201,253202,253203,253204,253205,253206,253207,253208,253209,253210,318768,318813,318818,318821,318823,318827,318829,318831,318842,318844,253311,253312,253313,253314,318851,253315,318853,253316,253317,253318,253319,253320,253321,253322,253323,253324,253325,253326,253327,253328,253329,253330,253331,318870,318871,253332,253333,253334,318875,253335,318877,318878,253336,253337,253338,253339,253340,318884,253341,253342,253343,253344,253345,253346,253347,318892,253348,253349,253350,253351,253352,318898,253354,253355,253356,253357,253358,253359,318905,253361,253362,253363,253364,253365,253366,253367,253368,253369,253370,253371,253372,253373,253374,253375,253376,253377,253378,253379,253380,318926,253382,253383,253384,253385,253386,253387,253388,253389,318935,253391,253392,318938,253394,253395,253396,253397,253398,253399,253400,253401,253402,253403,253404,318950,253406,253407,253408,253409,253410,318956,318968,318971,318972,318973,318974,318975,318977,318983,318989,318990,318997,319008,319012,319024,319034,319035,319038,253511,319048,253512,319050,253513,253514,319053,253515,253516,253517,253518,253519,253520,253521,253522,253523,319063,253524,319065,253525,253526,253527,253528,253529,253530,253531,253532,319074,253533,253534,319077,319078,253535,253536,253537,319082,253538,253539,253540,319086,253542,253543,253544,253545,319091,253547,253548,253549,253550,253551,253552,253553,253554,253555,253556,319102,253558,253559,253560,253561,253562,319108,253564,253565,253566,253567,253568,253569,253570,253571,253572,253573,253574,253575,253576,253577,253578,253579,253580,253581,253582,253583,253584,253585,253586,253587,253588,253589,253590,253591,253592,253593,253594,253595,253596,253597,319143,253599,253600,253601,319147,253603,319149,253605,253606,253607,253608,253609,253610,319166,319173,319178,319182,319184,319231,319239,319240,319245,253711,253712,253713,319250,253714,253715,253716,253717,253718,253719,253720,253721,253722,253723,253724,253725,253726,253727,253728,253729,253730,253731,319269,253732,253733,253734,253735,253736,319275,253737,253738,253739,319279,253740,253741,253742,253743,319285,253744,253745,253746,253747,253748,253749,319292,253750,253751,253752,253753,253754,253755,253756,253757,253758,253759,319303,253760,253761,253762,253763,253764,253765,253766,319311,253767,253768,253769,253770,253771,253772,253773,253774,253775,253776,253777,253778,253779,253780,319326,319327,253783,253784,253785,253786,253787,253788,319334,253790,253791,253792,253793,253794,253795,319341,319342,253798,253799,253800,253801,253802,319348,253804,253805,319351,253807,319353,253809,253810,319356,319357,319361,319364,319370,319374,319379,319384,319396,319397,319406,319408,319411,319419,319421,319428,319435,319438,319440,319441,319450,319460,319461,319462,319463,319472,319482,319483,319490,319499,319539,319547,254011,254012,254013,254014]) UNION SELECT target_chain_id FROM event_auth_chain_links INNER JOIN links ON (chain_id = origin_chain_id))SELECT origin_chain_id, origin_sequence_number,target_chain_id, target_sequence_number FROM links INNER JOIN event_auth_chain_links ON (chain_id = origin_chain_id);

[pcouy]

I'm running 1.120.2 and randomly get the same issue. It just happened to me. @parisni Is it a coincidence that you just commented on this issue less than 20 minutes ago, or did you also experience this at the same time as I did ? (See the graph below, time is UTC+1)

It seems the RAM usage of my postgres container started to rise shorlty before the CPU did.

[parisni]

a coincidence that you just commented on this issue less than 20 minutes ago, or did you also experience this at the same time as I did

We definitely had the same issue at the same time. Not sure if it's independant. I have no idea what kind of action triggers this recursive query.

…

On December 10, 2024 5:06:01 PM GMT+01:00, Pierre Couy ***@***.***> wrote: I'm running 1.120.2 and randomly get the same issue. It just happened to me. @parisni Is it a coincidence that you just commented on this issue less than 20 minutes ago, or did you also experience this at the same time as I did ?  It seems the RAM usage of my postgres container started to rise shorlty before the CPU did.

[pcouy]

We definitely had the same issue at the same time. Not sure if it's independant. I have no idea what kind of action triggers this recursive query.

This is a strong indicator that it was triggered by a cause external to our servers. I checked my synapse log but could not find any particularly weird line in all the noise. I'm not sure if this is caused by a single server acting funny (either old, misconfigured or malicious) or if the federation kind of collectively caused it.

Since we both had it happen at the same time, I would be very interested to hear from people to whom it did NOT happen at the same time. There is probably something to learn about what triggers it by understanding the differences between impacted and not-impacted servers.

(maybe we should create a room for server admins dedicated to tracking and sharing when this happens)

[parisni]

@pcouy having the issue right now

[MichaelSasser]

We are pretty sure, were able to trigger this behavior by letting Mjölnir change the room server ACLs (we always added a couple of them at a time and other than that, there wasn't going on anything really on the homeserver). After restarting synapse, CPU usage of Postgres drops again to normal until changing the ACLs once more.

pg_stat_activity Example Query

psql (17.4)
Type "help" for help.

synapse=# \x
synapse=# SELECT * FROM pg_stat_activity WHERE datname = 'synapse' AND state = 'active' AND usename = 'synapse'; -- might need a couple of tries
-[ RECORD 1 ]----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid            | 16386
datname          | synapse
pid              | 83559
leader_pid       |
usesysid         | 16385
usename          | synapse
application_name |
client_addr      | 172.29.0.5
client_hostname  |
client_port      | 37726
backend_start    | 2025-05-15 10:59:35.568225+00
xact_start       | 2025-05-19 12:02:43.574523+00
query_start      | 2025-05-19 12:02:44.991597+00
state_change     | 2025-05-19 12:02:44.991604+00
wait_event_type  | IO
wait_event       | BuffileWrite
state            | active
backend_xid      |
backend_xmin     | 24350111
query_id         |
query            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           +
                 |             WITH RECURSIVE links(chain_id) AS (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           +
                 |                 SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    +
                 |                     DISTINCT origin_chain_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                 |                 FROM event_auth_chain_links WHERE origin_chain_id = ANY(ARRAY[262144,262145,262146,262147,262148,262149,262150,262151,262152,262153,262154,262155,262156,262157,262158,262159,262160,262161,262162,262163,262164,262165,262166,262167,262168,262169,262170,262171,262172,262173,262174,262175,262176,262177,262178,262179,262180,262181,262182,262183,262184,262185,262186,262187,262188,262189,262190,262191,262192,262193,262194,262195,262196,262197,262198,262199,262200,262201,262202,262203,262204,262205,262206,262207,262208,262209,262210,262211,262212,262213,262214,262215,262216,262217,262218,262219,262220,262221,262222,262223,262224,262225,262226,262227,262228,262229,262230,262231,262232,262233,262234,262235,262236,262237,262238,262239,262240,262241,262242,262243,262244,262245,262246,262247,262248,262249,262250,262251,262252,262253,262254,262255,262256,262257,262258,262259,262260,262261,26
backend_type     | client backend

Synapse Dashboard - Database Row

(UTC+02:00)

Database Overview Rows

(UTC+02:00)

Additional Information

We were able to trigger this three times now on three different days. The messages mjolnir ban domain.tld foo (just using: msgtype and body) were created, each with a delay of 7 seconds, by a little bash script I wrote years ago. The number of ACL's being added ranges from 20-128 in a row. None of the servers added to the ACLs were (at the time) part of the protected rooms at the time. The number of current room members of the rooms the ACLs were changed ranges from 200 to 20000.

Protected rooms

• Python: !iuyQXswfjgxQMZGrfQ:matrix.org
• Community (Space): !JnlWrgspTvEytPctFM:matrix.org
• Offtopic: !CGjuXMiLwtPOTXYexo:matrix.org
• Meta: !PyPOqShkhhfncUgWjN:matrix.org

Policy list room over time

(times in UTC+02:00; room: !HhKjLjojOKSiiMFLkI:michaelsasser.org)

Synapse v1.129.0
Mjolnir v1.10.0 (and the version right before that)