Finding nearest point for each distinct category in performant way using big PostGIS table?

Question

I have a table, etablissements with millions of rows, and a geom (st_point) column , spatially indexed (using gist). Those features may have a different category, activite_principale.

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

Here's what I did right now:

    (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 then 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 planning is much shorter and the execution too but it's still slow (350-450ms).

I investigated compound index on geom & another text column, but that does not seem to be possible today ?

I use postgres 10 & postgis 2.4.

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

Answer 1

I´m curious, what does your plan say if you run

SELECT DISTINCT ON (a.activite_principale)
       a.activite_principale,
       a.id
FROM geo_data.etablissements AS a
WHERE a.activite_principale IN ('1071C', '4711D', <3rd>, <4th>, <5th>)
ORDER BY a.activite_principale,
         a.geom <-> 'SRID=32632;POINT(363982.8087 5623158.5124)'::geometry

(Fill in <3rd>, <4th>, <5th>)