Hey @sebbo,
that timings would be a dream 
After some testing (this time with a local minikube, and only two assets) i think i’ve found the bottleneck.
I’ve enabled the query log this way:
Neos:
Flow:
persistence:
doctrine:
sqlLogger: 'Neos\Flow\Persistence\Doctrine\Logging\SqlLogger'
log:
psr3:
loggerFactory: Neos\Flow\Log\PsrLoggerFactory
'Neos\Flow\Log\PsrLoggerFactory':
sqlLogger:
default:
options:
severityThreshold: '%LOG_DEBUG%'
And i’ve focussed only on the media part, which generated queries such as the asset count:
SELECT count(a.persistence_object_identifier) c FROM neos_media_domain_model_asset a WHERE a.dtype NOT IN('neos_media_imagevariant')
I’m using some Policies to restrict media types being able to be uploaded. First i’ve blamed them, anyway removing the Policy didn’t brought up any advantage.
Query 2:
SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0, n0_.lastmodified AS lastmodified_1, n0_.title AS title_2, n0_.caption AS caption_3, n0_.copyrightnotice AS copyrightnotice_4, n0_.assetsourceidentifier AS assetsourceidentifier_5, n3_.width AS width_6, n3_.height AS height_7, n4_.name AS name_8, n4_.presetidentifier AS presetidentifier_9, n4_.presetvariantname AS presetvariantname_10, n4_.width AS width_11, n4_.height AS height_12, n5_.width AS width_13, n5_.height AS height_14, n0_.dtype AS dtype_15, n0_.resource AS resource_16, n4_.originalasset AS originalasset_17 FROM neos_media_domain_model_asset n0_ LEFT JOIN neos_media_domain_model_audio n1_ ON n0_.persistence_object_identifier = n1_.persistence_object_identifier LEFT JOIN neos_media_domain_model_document n2_ ON n0_.persistence_object_identifier = n2_.persistence_object_identifier LEFT JOIN neos_media_domain_model_image n3_ ON n0_.persistence_object_identifier = n3_.persistence_object_identifier LEFT JOIN neos_media_domain_model_imagevariant n4_ ON n0_.persistence_object_identifier = n4_.persistence_object_identifier LEFT JOIN neos_media_domain_model_video n5_ ON n0_.persistence_object_identifier = n5_.persistence_object_identifier WHERE (n0_.dtype NOT IN ('neos_media_imagevariant') AND n0_.assetsourceidentifier = ? AND n0_.dtype NOT IN ('neos_media_imagevariant')) AND ( ( NOT (( (n0_.resource IN (SELECT subselectd271cfb764051c57942d2dcbef891f2d.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/pdf') AS subselectd271cfb764051c57942d2dcbef891f2d ) ) AND n0_.persistence_object_identifier IN (
SELECT n0__a.persistence_object_identifier
FROM neos_media_domain_model_asset AS n0__a
LEFT JOIN neos_media_domain_model_asset_tags_join n0__atj ON n0__a.persistence_object_identifier = n0__atj.media_asset
LEFT JOIN neos_media_domain_model_tag n0__t ON n0__t.persistence_object_identifier = n0__atj.media_tag
WHERE n0__t.label = 'confidential')))) AND ( NOT ( (n0_.resource IN (SELECT subselecta985f45676418ea46586e05848596203.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/javascript') AS subselecta985f45676418ea46586e05848596203 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselectf3363e2cbd1f1f08c9dc971df9f9cce3.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/json') AS subselectf3363e2cbd1f1f08c9dc971df9f9cce3 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect7469e20917d8b189ec50f416d9781119.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/msword') AS subselect7469e20917d8b189ec50f416d9781119 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselecte1ee8345e9bb75fab9e6bad0014b65cb.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document') AS subselecte1ee8345e9bb75fab9e6bad0014b65cb ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect95b734ed401e58aee21e586ea6533596.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/msexcel') AS subselect95b734ed401e58aee21e586ea6533596 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselecte2e1f495f80ba93abaae5c7783b3cb24.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') AS subselecte2e1f495f80ba93abaae5c7783b3cb24 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect0a9a572de6d2e40b9d2d7c21ffcd6eed.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/mspowerpoint') AS subselect0a9a572de6d2e40b9d2d7c21ffcd6eed ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect1eab7e8daa0e843a61c897c460567b1d.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/vnd.openxmlformats-officedocument.presentationml.presentation') AS subselect1eab7e8daa0e843a61c897c460567b1d ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect43783aede78ca4341ce63422f427cb1d.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/gzip') AS subselect43783aede78ca4341ce63422f427cb1d ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect342218692571af7ab050ddd758415de8.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/x-macbinary') AS subselect342218692571af7ab050ddd758415de8 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect0b533bb5ba2fae8ec762833b919587e0.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/xhtml+xml') AS subselect0b533bb5ba2fae8ec762833b919587e0 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselecte6dff56a4040631c30817f827dff71fb.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/x-httpd-php') AS subselecte6dff56a4040631c30817f827dff71fb ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselectcd2a9205cad5002477f2023b55083531.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'application/force-download') AS subselectcd2a9205cad5002477f2023b55083531 ) ) )) AND ( NOT ( (n0_.resource IN (SELECT subselect9ae6a0c504c6b617d6a55e7fb8b416b6.persistence_object_identifier_0 FROM (SELECT n0_.persistence_object_identifier AS persistence_object_identifier_0 FROM neos_flow_resourcemanagement_persistentresource n0_ WHERE n0_.mediatype = 'image/gif') AS subselect9ae6a0c504c6b617d6a55e7fb8b416b6 ) ) )) ) ORDER BY n0_.lastmodified DESC LIMIT 20
At the end, i was running the same command from my neos pod to connect to the postgresql pod (both are running in the same namespace):
Query 1:
time psql -h postgresql -p 64000 -U postgresqluser -d databasename -c "EXPLAIN ANALYZE SELECT count(a.persistence_object_identifier) c FROM neos_media_domain_model_asset a WHERE a.dtype NOT IN('n
eos_media_imagevariant')"
Password for user postgresqluser:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10.60..10.61 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)
-> Seq Scan on neos_media_domain_model_asset a (cost=0.00..10.50 rows=39 width=98) (actual time=0.008..0.009 rows=2 loops=1)
Filter: ((dtype)::text <> 'neos_media_imagevariant'::text)
Rows Removed by Filter: 2
Planning Time: 0.207 ms
Execution Time: 0.072 ms
(6 rows)
real 0m 5.11s
user 0m 0.01s
sys 0m 0.00s
Query 2:
/var/Neos $ time psql -h postgresql -p 64000 -U postgresqluser -d databasename -c "EXPLAIN ANALYZE SELECT n0_.persistence_object_identifier, n0_.lastmodified, n0_.title, n0_.caption, n0_.copyrightnotice, n0_
.assetsourceidentifier, n3_.width, n3_.height, n4_.name, n4_.presetidentifier, n4_.presetvariantname, n4_.width, n4_.height, n5_.width, n5_.height, n0_.dtype, n0_.resource, n4_.originalasset
> FROM neos_media_domain_model_asset n0_
> LEFT JOIN neos_media_domain_model_audio n1_ ON n0_.persistence_object_identifier = n1_.persistence_object_identifier
> LEFT JOIN neos_media_domain_model_document n2_ ON n0_.persistence_object_identifier = n2_.persistence_object_identifier
> LEFT JOIN neos_media_domain_model_image n3_ ON n0_.persistence_object_identifier = n3_.persistence_object_identifier
> LEFT JOIN neos_media_domain_model_imagevariant n4_ ON n0_.persistence_object_identifier = n4_.persistence_object_identifier
> LEFT JOIN neos_media_domain_model_video n5_ ON n0_.persistence_object_identifier = n5_.persistence_object_identifier
> WHERE n0_.dtype NOT IN ('neos_media_imagevariant')
> AND n0_.assetsourceidentifier = 'neos'
> ORDER BY n0_.lastmodified DESC LIMIT 20"
Password for user postgresqluser:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=49.49..49.54 rows=20 width=3486) (actual time=0.064..0.066 rows=2 loops=1)
-> Sort (cost=49.49..50.05 rows=225 width=3486) (actual time=0.064..0.065 rows=2 loops=1)
Sort Key: n0_.lastmodified DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.45..43.50 rows=225 width=3486) (actual time=0.041..0.046 rows=2 loops=1)
-> Nested Loop Left Join (cost=0.29..27.34 rows=15 width=3478) (actual time=0.031..0.035 rows=2 loops=1)
-> Nested Loop Left Join (cost=0.14..19.15 rows=1 width=3470) (actual time=0.026..0.029 rows=2 loops=1)
-> Seq Scan on neos_media_domain_model_asset n0_ (cost=0.00..10.60 rows=1 width=1816) (actual time=0.008..0.010 rows=2 loops=1)
Filter: (((dtype)::text <> 'neos_media_imagevariant'::text) AND ((assetsourceidentifier)::text = 'neos'::text))
Rows Removed by Filter: 2
-> Index Scan using typo3_media_domain_model_imagevariant_pkey on neos_media_domain_model_imagevariant n4_ (cost=0.14..8.16 rows=1 width=1752) (actual time=0.008..0.008 rows=0 loops=2)
Index Cond: ((persistence_object_identifier)::text = (n0_.persistence_object_identifier)::text)
-> Index Scan using typo3_media_domain_model_image_pkey on neos_media_domain_model_image n3_ (cost=0.15..8.17 rows=1 width=106) (actual time=0.003..0.003 rows=1 loops=2)
Index Cond: ((persistence_object_identifier)::text = (n0_.persistence_object_identifier)::text)
-> Memoize (cost=0.16..8.18 rows=1 width=106) (actual time=0.005..0.005 rows=0 loops=2)
Cache Key: n0_.persistence_object_identifier
Cache Mode: logical
Hits: 0 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using typo3_media_domain_model_video_pkey on neos_media_domain_model_video n5_ (cost=0.15..8.17 rows=1 width=106) (actual time=0.002..0.002 rows=0 loops=2)
Index Cond: ((persistence_object_identifier)::text = (n0_.persistence_object_identifier)::text)
Planning Time: 0.748 ms
Execution Time: 0.123 ms
(22 rows)
real 0m 7.97s
user 0m 0.00s
sys 0m 0.01s
It looks like the connection between neos and postgresql is very slow. Not sure how to fix this, yet.
