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