The optimization process and recommendations:

1. Avoid Subqueries (query line: 4): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
2. Avoid Subqueries In From Clause (modified query below): The database cannot properly optimize subqueries in the FROM clause. Therefore, we recommend to extract the subqueries to temporary tables, index them and join to them in the outer query.
3. 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.
4. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
5. Push Filtering Conditions Into Subqueries (modified query below): Parts of the WHERE clause can pushed from the outer query to a subquery / union clause. Applying those conditions as early as possible will allow the database to scan less data and run the query more efficiently.
6. Use Numeric Column Types For Numeric Values (query line: 87): Referencing a numeric value (e.g. 302857893) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.

Optimal indexes for this query:

ALTER TABLE `es_temp1` ADD INDEX `es_temp1_idx_id` (`id`);
ALTER TABLE `es_temp2` ADD INDEX `es_temp2_idx_id` (`id`);
ALTER TABLE `valyuta_CONTRACT_SUB_TYPES` ADD INDEX `valyuta_sub_idx_type_short_name` (`type`,`short_name`);
ALTER TABLE `valyuta_GOODS` ADD INDEX `valyuta_goods_idx_declaration_id_id` (`DECLARATION_ID`,`id`);
ALTER TABLE `valyuta_declarations` ADD INDEX `valyuta_declaratio_idx_type_id_declaratio` (`type`,`id`,`declaration_date`);

The optimized query:

SELECT
        * 
    FROM
        (SELECT
            t.id,
            t.num,
            t.declaration_Date,
            ct.type,
            g.name,
            g.tnved_code,
            g.g31_amount,
            g.brutto,
            g.netto,
            CASE 
                WHEN t.state = 1 THEN 1 
                ELSE 0 END env,
CASE 
    WHEN ct.type = 1 THEN t.g2_code2 
    ELSE '200794867' END AS seller_tin,
CASE 
    WHEN ct.type = 1 THEN '200794867' 
    ELSE t.g8_code2 END AS buyer_tin,
CASE 
    WHEN ct.type = 1 THEN t.g2_name 
    ELSE t.g8_name END AS seller_name,
CASE 
    WHEN ct.type = 2 THEN t.g8_name 
    ELSE t.g2_name END AS buyer_name,
sum(g.cost_facture * t.curr_Course) AS cost_Facture,
SUM(g1.good_payment_27 + g2.good_pay_late_27) AS good_payment_27,
SUM(g1.good_payment_29 + g2.good_pay_late_29) AS good_payment_29 
FROM
valyuta_declarations t,
valyuta_CONTRACT_SUB_TYPES ct,
valyuta_GOODS g,
es_temp1 g1,
es_temp2 g2 
WHERE
(
    (
        t.type = ct.short_name 
        AND t.id = g.DECLARATION_ID 
        AND g.id = g1.id 
        AND g.id = g2.id 
        AND (
            (
                ct.type = 1 
                AND t.g1_b IN (
                    10, 11
                )
            ) 
            OR (
                ct.type = 2 
                AND t.g1_b = 40
            )
        )
    ) 
    AND (
        ct.type = 1
    )
) 
AND (
    t.declaration_date BETWEEN to_date('01-01-2022', 'dd-mm-yyyy') AND to_date('31-03-2022', 'dd-mm-yyyy')
) 
GROUP BY
t.id,
t.num,
t.declaration_Date,
t.state,
ct.type,
g.name,
g.tnved_code,
g.g31_amount,
g.brutto,
g.netto,
t.G2_CODE2,
t.G2_NAME,
t.g8_code2,
t.g8_name 
ORDER BY
NULL) a 
WHERE
1 = 1 
AND a.env = 1 
AND 1 = 1 
AND seller_tin IN (
'302857893'
)