Improve search performance by using lateral join

This way the window function is not done on all pictures, but only the resulting subset.

I also decreased the default limit from 10 000 items to 10 which seems a more sensible default and is the default defined in the STAC spec (but the maximum limit is still 10 000 items). This default seems a bit low, but since it's default in the spec, I think we should keep it (and it will make default queries lots faster)

Here is a simple benchmark (done with a backup of the IGN instance, on a local machine, without concurrent user, and all query done 5 times):

label lateral join (ms) develop (ms)
search large BBOX limit 10 000 3736 10027 <- timeout
search large BBOX limit 10 38 6324
Small BBOX (106 features) + limit 10 000 73 8651
Small BBOX (106 features) + limit 10 30 2474
search biggest collection (~17 000 features) limit 10 000 3932 10027
search biggest collection (~17 000 features) limit 10 38 10027
search medium collection (~1400 features) limit 10 000 3326 10027
search medium collection (~1400 features) limit 10 40 10027
search by id 51 47
search by 10 id 24 8847

So the change seems worth it.

Edited by antoine-de

Merge request reports

Loading