The buffer-intersection construct in general is better be avoided in favor of the (for some reason not always index driven, but still) more efficient
ST_DWithin. However, you will still need to implement a table self-join, and that will imply unnecessary overhead in this case.
I suggest to look into
ST_ClusterDBSCAN instead; note that this will only work if your data is in a (metric) projection, as e.g. threshold values passed in will be assumed to be in the data's CRS units (and geography data type is not accepted).
ST_ClusterDBSCAN(geom, 0.8, 1) OVER(PARTITION BY <column>) AS clst
will select your table and assign an integer in column
clst to each row, clustered by max distance of 0.8 units (
eps parameter) and partitioned by
But note: the
clst will be in the range of
[0 - n] for each distinct value in
<value_a> -> [0 - na],
<value_b> -> [0 - nb]), so grouping will need to be based on both! You can, however, easily add more attributes to cluster by to the
PARTITION BY list or
ORDER BY whatever, if you need to, just as with any other window function.
minpoints parameter will exclude (set to
NULL) all geometries that have less cluster partners than the given value; set accordingly (e.g. if you want every geometry to be assigned a
clst number, leave at 1; if you only want to consider clusters of more or equal to five fitting geometries, set to 5).
This might effectively be only the first but more efficient step towards your goal and can be used in a subquery or CTE; from here, a
ST_UnaryUnion based on equal
clst might or might not be what you want; if you don't know how to proceed, just write a comment and I'll try to expand the answer accordingly.