tl;dr: Try the query under However first. This will, if even, only make sense if the blade geometry doesn't need dissolving and ST_Union can be substituted with ST_Collect.
Following my comments:
What I was refering to is to 'outsource' the union into a CTE, that is, a WITH clause, like so:
WITH
blade AS (
SELECT ST_Collect(wkb_geometry) AS geom
FROM b
WHERE group_id = 43 and type_id = 12
)
SELECT a.someid,
ST_Difference(a.wkb_geometry, b.geom) AS geom
FROM a
JOIN blade AS b
ON ST_Intersects(a.wkb_geometry, b.geom);
A CTE will be executed once for each transaction, and can be seen as a temp table dropped on commit; this helps avoiding creating sets of rows over and over, and it looks better.
I used ST_Collect instead of ST_Union; in most cases, this should return the same MULTI geometry while being orders of magnitude faster.
I also used a (INNER) JOIN instead of a WHERE filter; this is merely for readability, in most cases.
Note also that there are no (possibly costly) GROUP BY operations.
However:
The main motor for performance, and actually the guy doing your job, is the Query Planner; the Planner will highly optimize your query by flattening, replacing, moving and even not executing parts of it. And it is mostly right.
Its efficiency is mainly bound to up-to-date table stats; you can (and should, e.g. after creating indexes or UPDATE/INSERT/DELETE operations) update those manually by executing VACUUM ANALYZE/FULL on one or all tables.
Now, the key point; there are queries the planner cannot optimize, by design or logic, and CTEs are one of those (functions are another, so e.g. the union in your difference function cannot be optimized even if possible).
In your case, union within an actual subquery can thus be beneficial (and the better choice):
SELECT a.someid,
ST_Difference(a.wkb_geometry, b.geom) AS geom
FROM a
JOIN (
SELECT ST_Collect(wkb_geometry) AS geom
FROM b
WHERE group_id = 43 and type_id = 12
) AS b
ON ST_Intersects(a.wkb_geometry, b.geom);
Notes on index and performance:
In general, running geometric relation and/or manipulation queries on large and/or MULTI geometries perform worse than on many small single geometries; for each input point set, every vertex of the large geometry has to be traversed and checked.
Since a spatial index works with bbox trees, filtering (non-)relation is blazingly fast for small geometries by simply comparing only four vertices in a tree branch, and only do the geometric intersection test on a few chosen ones; having a very large bbox however will most likely include many or all compared others, even if the geometries themselves don't intersect geographically. PostGIS will then still have to do the costly intersection filter on too many geometries, defeating the purpose of an index.
That being said, ST_Difference is tricky, since it best works with the blade being a unified geometry (to avoid cross product slicing of the same geometry, resulting in duplicates). A union from indexed geometries will render the combined gemetry non-SARGable, that is, it cannot have or utilize an index.
But it is possible that, when working with a subset where each geometry is guaranteed to intersect with the blade, a union of those will perform equally or even faster, as there is no (in this case unnecessary) index lookup first, and having that second large MULTI geometry then in memory already.
Considering this, running:
SELECT ST_Difference(a.geom, b.geom) AS geom
FROM (
SELECT ST_Collect(wkb_geometry) AS geom
FROM a
WHERE <column_filter_condition>
) AS a
CROSS JOIN (
SELECT ST_Collect(wkb_geometry) AS geom
FROM b
WHERE group_id = 43 and type_id = 12
) AS b;
might even be faster, if you can filter a in advance (without checking for intersection) into an overlapping subset.
This quickly got out of hand in scope, not sure if this will even help. But since you asked...
Could you reply if any of the above did perform better? ...or failed to work?
