OrderBy optimization

[ndevilleBE]

Dear,
I recently migrated all my Sensorthings data to a TimescaleDB and working on the optimization of response time.
I noticed that when I call
(https://sensors.naturalsciences.be/sta/v1.1/Things(1)/Datastreams?$expand=Observations($filter=phenomenonTime%20gt%202024-03-19T15:31:00.000000Z%20and%20phenomenonTime%20lt%202024-03-19T15:33:00.000000Z))

The API will query each datastream from the Thing = 1 with this query:

select "e0"."RESULT_BOOLEAN", "e0"."RESULT_QUALITY", "e0"."PHENOMENON_TIME_START", "e0"."PARAMETERS", "e0"."DATASTREAM_ID", "e0"."RESULT_STRING", "e0"."RESULT_TYPE", "e0"."VALID_TIME_END", "e0"."PHENOMENON_TIME_END", "e0"."FEATURE_ID", "e0"."ID", "e0"."RESULT_JSON", "e0"."RESULT_TIME", "e0"."RESULT_NUMBER", "e0"."VALID_TIME_START", "e0"."MULTI_DATASTREAM_ID" from "OBSERVATIONS" as "e0" left outer join "DATASTREAMS" as "e1" on "e1"."ID" = "e0"."DATASTREAM_ID" where ("e0"."DATASTREAM_ID" = 7731 and "e0"."PHENOMENON_TIME_START" > timestamp '2024-03-19 12:31:00.0' and "e0"."PHENOMENON_TIME_END" <= timestamp '2024-03-19 12:33:00.0' and "e0"."PHENOMENON_TIME_START" < timestamp '2024-03-19 12:33:00.0') order by "e0"."PHENOMENON_TIME_START" asc, "e0"."PHENOMENON_TIME_END" asc, "e0"."ID" asc offset 0 rows fetch next 101 rows only

The slowing part in this query is the sorting at the end of the query order by "e0"."PHENOMENON_TIME_START" asc, "e0"."PHENOMENON_TIME_END" asc, "e0"."ID" asc

The processing time is multiplied by 40 when sorting on the three values compared to only 1 (using Explain in PostgreSQL).
Do you know of a way to change the URL to only sort on the Phenomenon_time_start asc?

Thanks

[hylkevds]

Turn off the setting alwaysOrderbyId, this will remove any automatic sorting.
Note that this does mean there is no guaranteed stable sorting, which may cause a problem with pagination.
It will also make pagination slower (unless you manually add $orderby=id), since without stable sorting it can only use $top, and has to explicitly find all items to skip.

You may be better off looking at adding an index that better fits this query, though I am a bit surprised it's not using the existing index on PHENOMENON_TIME_START.

[ndevilleBE]

Dear Hylke,
Thank you for your answer. Improving the index with the inclusion of phenomenon_time_end improve the response time.