Optimize slow SQL query
I suspect one bottleneck is the grouping in the subqueries. For each row in the ianc_fons table the rows are being duplicated - one for each join the chile table ianc_unicat. And then they are being grouped again.

I would try a different approach and move the summing into a joined subquery :

SELECT f.codi_arxiu,
       f.nom_arxiu,
       f.codi_grup,
       f.codi_fons_refe,
       f.codi_fons,
       f.nom_fons,
       f.any_ini,
       f.any_fi,
       f.cronologia,
       child.num_uni_imatges,
       child.unitats_text
 FROM ianc_fons f
 LEFT JOIN
      ( SELECT codi_arxiu, codi_fons,
               SUM (CASE WHEN u.num_imatges &gt; 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
               COUNT (u.codi_unitat) AS unitats_text
      FROM ianc_unicat u 
      WHERE u.anc_unicat_id IN
                    (SELECT u.anc_unicat_id
                       FROM ianc_unicat u
                      WHERE     1 = 1
                            AND u.codi_arxiu = '1'
                            AND u.codi_grup = 'ANC'
                            AND u.codi_fons = 1)
      GROUP BY codi_arxiu, codi_fons
      ) AS child ON f.codi_arxiu = child.codi_arxiu AND f.codi_fons = child.codi_fons


Note I have removed the DISTINCT from the Select from ianc_unicat. As this is just used in the filtering, the DISTINCT is not necessary. Using DISTINCT is always an overhead as the query engine needs to sort the records and then compare them all one by one to remove duplicates. Avoid as much as possible.

Each side of the UNION is selecting from the same table, and then summing different child records.

As the summing is now in a separate joined subquery, you can get rid of the union, and just join in the other table in a similar fashion. The Select will now need to sum up the totals from both joined subqueries.

SELECT f.codi_arxiu,
       f.nom_arxiu,
       f.codi_grup,
       f.codi_fons_refe,
       f.codi_fons,
       f.nom_fons,
       f.any_ini,
       f.any_fi,
       f.cronologia,
       COALESCE(child.num_uni_imatges, 0) + COALESCE(child2.num_uni_imatges, 0) AS num_uni_imatges,
       child.unitats_text,
       child2.unitats_notext


Note the use of COALESCE. As you are left joining these totals now, there is a chance that the values may be Null. Null is not the same as 0. Null + 4 would not equal 4, the result would be Null. So the COALESCE is necessary to make Null values 0.

Now you no longer need the outer select as this was being used to just sum the rows within the union. You can get rid of this all together. The row joined in to the outer query can just be joined into our main query. We no longer the the extra grouping here either. This gives us a query similar to the following :

SELECT f.codi_arxiu,
       f.nom_arxiu,
       f.codi_grup,
       f.codi_fons_refe,
       f.codi_fons,
       f.nom_fons,
       f.any_ini,
       f.any_fi,
       f.cronologia,
       COALESCE(child.num_uni_imatges, 0) + COALESCE(child2.num_uni_imatges, 0) AS num_uni_imatges,
       child.unitats_text,
       child2.unitats_notext
 FROM ianc_fons f
 LEFT JOIN
      ( SELECT codi_arxiu, codi_fons,
               SUM (CASE WHEN u.num_imatges &gt; 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
               COUNT (u.codi_unitat) AS unitats_text
      FROM ianc_unicat u 
      WHERE u.anc_unicat_id IN
                    (SELECT u.anc_unicat_id
                       FROM ianc_unicat u
                      WHERE     1 = 1
                            AND u.codi_arxiu = '1'
                            AND u.codi_grup = 'ANC'
                            AND u.codi_fons = 1)
      GROUP BY codi_arxiu, codi_fons
      ) AS child ON f.codi_arxiu = child.codi_arxiu AND f.codi_fons = child.codi_fons

 LEFT JOIN 
      ( SELECT u.nt1_codi_arxiu, u.codi_fons,
               SUM (CASE WHEN u.num_imatges &gt; 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
               COUNT (u.nt1_unitat) AS unitats_notext
        FROM ianci_unicats u 
       WHERE     1 = 1
             AND u.nt1_id IN
                    (SELECT u.nt1_id
                       FROM ianci_unicats u
                        WHERE     1 = 1
                            AND u.nt1_codi_arxiu = '1'
                            AND u.codi_grup = 'ANC'
                            AND u.nt1_codi_fons = 1)
      ) AS child2 ON f.codi_arxiu = child2.nt1_codi_arxiu AND f.codi_fons = child2.nt1_codi_fons

  LEFT JOIN v_ianc_arxius_order v ON ( f.codi_arxiu=v.codi_arxiu )
  ORDER BY v.order_ANC,v.order_ANC2,f.nom_arxiu, f.nom_fons