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

So here's a simplified version of the table:

So these are samples taken with a ~10 year span. So 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 to long.

Really thankful for your'e help!

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.