Point set intersections are _very costly_, and you want to absolutely make sure you limit your queries
- to *the least possible function calls*
- on *the least possible amount of vertices* between both participating geometries

For this you want to
- first `ST_SubDivide` your *MultiPolygon*, resulting in some 300 times less vertices per operation
- then explicitly `JOIN` on `ST_Intersects` to make use of the now more effective index on `landcoverpolygons.geom`, limiting operations to only the relevant subset
- while using a `CASE` decision to avoid any unnecessary call to `ST_Intersection`
- and finally get your `ST_Centroid` on the `ST_Collect` of those individual intersections that you mention above

Note that
- it doesn't make sense to transform your geometries _at all_ - _especially_ if `landcoverpolygons.geom` is actually referenced in `EPSG:4326`
- there is no benefit in any of your spatial indexes if more than a few percent of rows would match, and even if the planer chose to use it over the attribute indexes on your relations, it would be highly ineffective, producing a high number of false hits
- *Multi* geometries are _always_ bad for spatial relationship analysis, and even more so are geometries with excessive amounts of vertices
- adding indexes by guess is likely having the opposite effect - get rid of all excessive indexes, run `VACUUM ANALYZE` on all participating tables and retry; for this particular query only the indexes

    - `ON landcoverpolygons USING GIST (geom)`
    - `ON landcovertype (forest)`

  are of interest

Try:
```
WITH
    mp_sd AS (
        SELECT  ST_Subdivide(<MP>) AS geom
    )
SELECT  id,
        ST_Centroid(its_geom) AS geom
FROM    (
    SELECT  lcp.id,
            ST_Collect(
                CASE ST_Within(lcp.geom, mp_sd.geom)
                    WHEN TRUE THEN lcp.geom
                    ELSE ST_Intersection(lcp.geom, mp_sd.geom)
                END
            ) AS its_geom
    FROM    landcoverpolygons AS lcp
    JOIN    mp_sd
      ON    lcp.geom && mp_sd.geom AND ST_Intersects(lcp.geom, mp_sd.geom)
    WHERE   lcp.name_type <> 'foreign'
      AND   lcp.landcovertype IN (
                SELECT  landcovertype
                FROM    polygons_categories
                WHERE   forest
            )
    GROUP BY
            1
) q
;
```

Always post the full `EXPLAIN ANALYZE` output here - some of us know how to make sense of it!