# KNN search by distinct category in postgis/postgres

I have a postgres table, etablissements with millions of rows, and a geom (st_point) column , spatially indexed (using gist). I'm using the PostGIS extension. Those rows may have a different category, activite_principale.

I want to find from a given, fixed point (coordinates) the closest etablissements for 5 or 6 different categories called "activite_principale" (1 closest company per category).

Here's what I did right now and which is working :

    (WITH closest_candidates AS (
SELECT
ent.id,
ent.name,
ent.geom
FROM
geo_data.etablissements ent
WHERE ent.activite_principale = '1071C'
ORDER BY
ent.geom <->
'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
LIMIT 10
)
SELECT id
FROM closest_candidates
ORDER BY
ST_Distance(
geom,
'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
)
LIMIT 1)
UNION ALL
(WITH closest_candidates AS (
SELECT
ent.id,
ent.name,
ent.geom
FROM
geo_data.etablissements ent
WHERE ent.activite_principale = '4711D'
ORDER BY
ent.geom <->
'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
LIMIT 10
)
SELECT id
FROM closest_candidates
ORDER BY
ST_Distance(
geom,
'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
)
LIMIT 1)
--  UNION ALL
-- [...]  And so on...


I clustered the etablissements table around the geom spatial index and ran VACUUM ANALYZE geo_data.etablissements;

Here's the result of EXPLAIN ANALYZE after clustering.

The query planning is shorte and the execution too but it's still slow (350-450ms).

I use postgres 10 & postgis 2.4.

I'm not convinced my cascading UNION ALL is the best solution, that's why I'm asking. My problem : how to find the X closest points of table Y with distinct Z categories, and you are guaranteed each row is the closest X for category Z in PostGIS.

I don't know how to improve based on those explanations from the query planner. Can I do better performance ?

Note : this is a cross-post from the GIS stackexchange