greatest-n-per-group comparing geometry Postgis

Question

I'm working on a query that I think could fit under the greatest-n-per-group category.

Here's a simplified version of the table:

These are samples taken with a ~10 year span. I have the exact same geometry stored in two or more rows.

I'd like to fetch only one row for each unique geometry, and the hard part is that I only want the latest taken samples (according to year column) and the data on that row.

I have tried something like this:

SELECT *
 FROM samples s
 INNER JOIN (
SELECT geom, MAX(year) as year
FROM samples
GROUP BY geom
) b ON ST_EQUALS(s.geom, b.geom) AND s.year = b.year

WHERE customer_id = 38330
ORDER BY provnr ASC;

This seems to give me a pretty good result, but it takes a lot of time. This specific customer has ~850 rows in a table of ~400k rows. That query takes about 7 seconds. I have the geom and year columns indexed.

Is there a better way to write the query?

The query will be used in a real time app, so 7 seconds is way too long.

Answer 1

I would say that it is the order of the filtering that is causing the issues. It would appear that you are doing a GROUP BY geom over the entire table before filtering to the customer. This may also cause other issues if other customers have a geometry at that point as well. Another performance issue may be that you are joining the reduced group back onto samples using year and geom. I can't imagine this using indexes.

Assuming that there is a primary key type field or some unique identifier on the samples table I would use that against a sub query. The subquery makes use of the first_value windowed function to return the IDs of the latest years for a geom.

SELECT *
FROM SAMPLES
WHERE ID IN (
    SELECT FIRST_VALUE(ID) OVER (PARTITION BY GEOM ORDER BY YEAR DESC) ID
    FROM SAMPLES
    WHERE CUSTOMER = 38330
    )
ORDER BY PROVNR ASC;

Answer 2

A classic way to do this is to number the records in the ordered partitions and select the first ones:

SELECT * FROM 
  (SELECT *,
          ROW_NUMBER() OVER(PARTITION BY geom ORDER BY year DESC) AS row_num
     FROM samples WHERE customer = 38330) AS t
WHERE row_num = 1
ORDER BY provnr ASC;