This happens because Access::preloadPermissions() preloads permissions for ALL 9000 articles
(which is around 90MB from your screenshot)

I set it as bug, however I have no idea how it can be fixed.
But I can understand that it gives a really bad experience of use the content editing.

It probably for all Joomla versions. Cannot find older similar issue about it.

Any insights or workarounds to alleviate this issue would be greatly appreciated.

You can try hack the

However it is illegal. And I told you nothing, someone hacked my account. This message will self destroyed in few days

I tried changing $preload to $preload = false in joomla-cms/libraries/src/Access/Access.php at line 155, but the issue still persists.

Okay, I think now instead one large DB query you got a few hundrets of small DB queries. That is why it is still slow.

Another thing to try is to plreload only needed items, that in the current view.
Try, edit the articles list layout administrator/components/com_content/tmpl/articles/default.php
And place following code for preload, somewhere around line 70 (before the html rendering):

$accessWorkAround = new class () extends \Joomla\CMS\Access\Access
{
    // Preload only needed items
    public static function verySmartPreload($assetType, $assetsList, $key = 'id')
    {
        $extensionName = self::getExtensionNameFromAsset($assetType);

        /** @var \Joomla\Database\Mysql\MysqlDriver $db */
        $db    = Factory::getContainer()->get(\Joomla\Database\DatabaseInterface::class);
        /** @var \Joomla\Database\Mysql\MysqlQuery $query */
        $query = $db->getQuery(true);
        $query->select($db->quoteName(['id', 'name', 'rules', 'parent_id']))
            ->from($db->quoteName('#__assets'))
            ->where(
                $db->quoteName($key) . ' IN ('
                . implode(',', $query->bindArray($assetsList, \Joomla\Database\ParameterType::STRING)) . ')'
            );

        $assets = $db->setQuery($query)->loadObjectList();

        foreach ($assets as $asset) {
            self::$assetPermissionsParentIdMapping[$extensionName][$asset->id] = $asset;
            self::$preloadedAssets[$asset->id]                                 = $asset->name;
        }

        // Temporary mark as preloaded
        self::$preloadedAssetTypes[$assetType] = true;
    }

    public static function resetVerySmartPreload($assetType)
    {
        unset(self::$preloadedAssetTypes[$assetType]);
    }
};
$assetsList = [];
foreach ($this->items as $item) {
    $assetsList[] = 'com_content.article.' . $item->id;

    if (!empty($item->catid)) {
        $assetsList[] = 'com_content.category.' . $item->catid;
    }
}

$accessWorkAround::verySmartPreload('com_content.article', $assetsList, 'name');

then at the end of the file:

<?php $accessWorkAround::resetVerySmartPreload('com_content.article'); ?>

And please tell us if it help.
The editing form still will be slow, however the list view should be much faster.

After some testing, I think the actual slowdown is somewhere in ‎ArticlesModel::getListQuery() in the Administrator model

it takes a few seconds to load 20 articles from 40k on my test.
If I remove workflow Joins, it still more than a second.
Interestingly ‎ArticlesModel::getListQuery() in the Site model works much faster.
@richard67 do you have an idea what can be a bottleneck in this query?

Access preloading still preloads a lot, but it is doing it fast.

It sems a few things:

• workflow joins,
• languages joins,
• users joins,

When I removing them, everything works much faster.

Ah, it the same as #43701 I missed it, sorry.

@richard67 do you have an idea what can be a bottleneck in this query?

@Fedik No idea. Is already the part which you have linked (lines 232 to 266) so slow, without all that stuff which might be added later below? What I remember from some other issue is that it could be faster when changing the 3 inner joins to the workflow tables to left joins:

Ah, it the same as #43701 I missed it, sorry.

@Fedik Yes, that's the one where I saw the thing with inner or left join for the workflow tables.

Yea, with LEFT it 0.5s faster, but still slow in total 😄
Hmhm

it probably would be much smarter to split it to few queries:
load Articles,
load language data
load User data
load workflow data

Okay, maybe not that easy.

Kill workflow, problem solved :)

Kill workflow, problem solved

It is not only workflow

but it makes no sense to query workflow tables when its not being used.

Issue with access check hopefully will be fixed in #44078.
I close this issue for now. Thanks for reaport.