The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX crop_yield_idx_id_location ON "crop_yield" ("id_location");
CREATE INDEX culture_idx_id ON "culture" ("id");
CREATE INDEX location_idx_id ON "location" ("id");
CREATE INDEX microclimate_value_idx_id_culture ON "microclimate_value" ("id_culture");

The optimized query:

SELECT
        max(cy.value) AS yield,
        EXTRACT(YEAR 
    FROM
        cy.date) AS year 
    FROM
        microclimate_value AS mv 
    JOIN
        culture AS c 
            ON mv.id_culture = c.id 
    JOIN
        location AS l 
            ON mv.id_location = l.id 
    JOIN
        crop_yield AS cy 
            ON mv.id_location = cy.id_location 
    WHERE
        c.id = :cultureId 
        AND l.id = :locationId 
    GROUP BY
        year 
    ORDER BY
        year