1

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 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!

2

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;
  • Seems to work nicely, thank you! I'll try to wrap my head around your'e solution. – Simtrax Jun 20 '16 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.